xp cmdshell: xml

Showing posts with label xml. Show all posts

Friday, March 30, 2012

Problem returning HTMLEncoded text.

I am storing HTML inside a text field already HTMLEncoded. (example.
<body>) When I use the FOR XML EXPLICIT to return this field I get
<body> My text is being HTMLEncoded again.
Is there a way to prevent this from happening?
Thanks,
Ryan
Not to my knowledge, FOR XML automatically does the encoding.
Irwin Dolobowsky
Program Manager - SqlXml
http://weblogs.asp.net/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ryan Fiorini" <ryan.fiorini@.mutualofamerica.com> wrote in message
news:#o5U#iyFEHA.684@.tk2msftngp13.phx.gbl...
> I am storing HTML inside a text field already HTMLEncoded. (example.
> <body>) When I use the FOR XML EXPLICIT to return this field I get
> <body> My text is being HTMLEncoded again.
> Is there a way to prevent this from happening?
> Thanks,
> Ryan
>
|||Try to use the !xml directive in your FOR XML explicit query. This should
avoid entitization.
Best regards
Michael
"Irwin Dolobowsky[MS]" <irwind@.mail.microsoft.com> wrote in message
news:%233U1zw3FEHA.1600@.tk2msftngp13.phx.gbl...
> Not to my knowledge, FOR XML automatically does the encoding.
> --
> Irwin Dolobowsky
> Program Manager - SqlXml
> http://weblogs.asp.net/irwando
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Ryan Fiorini" <ryan.fiorini@.mutualofamerica.com> wrote in message
> news:#o5U#iyFEHA.684@.tk2msftngp13.phx.gbl...
get
>
sql

Monday, March 26, 2012

Problem reciving xml element

I hvae a problem receiving some xml data from my sql 2000 database and I
cant figure out why.The information is correct in the database and receives
all type of elements except one type.(Even dough this is declare the same).
Hope someone can help me with this problem.
Code:
Here is the xml beeing stored in the database:
<Soknadsskjema UtdanningCV="True" ErfaringCV="True" ID="112" xmlns="">
<Gruppe Gruppenavn="Personalia" SoknadsIDRef="112" GruppeID="81">
<Checkliste Navn="EgenCheck1" Tekst="boxer" CheckID="81" CheckListeID="10">
<ChecklisteElement Navn="EgenCheck1_1" Tekst="box1" DefaultMerket="False"
ChecklisteElementID="10" />
<ChecklisteElement Navn="EgenCheck1_2" Tekst="box2" DefaultMerket="False"
ChecklisteElementID="10" />
</Checkliste>
<Liste Navn="EgenListe1" Tekst="Listen" ListeID="81" ElementerID="13">
<ListeElement Navn="EgenListe1_1" Tekst="Liste1" ElementID="13" />
<ListeElement Navn="EgenListe1_2" Tekst="Liste2" ElementID="13" />
</Liste>
</Gruppe></Soknadsskjema>
This is the xsd schema (stillingsannonse6.xsd)
<?xml version="1.0" encoding="ISO-8859-1"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="GruppeRef" parent="Soknadsskjemaer" parent-key="ID"
child="Grupper" child-key="SoknadsIDRef" />
<sql:relationship name="GruppeListe" parent="Grupper" parent-key="GruppeID"
child="Lister" child-key="ListeID" />
<sql:relationship name="ListeElementRef" parent="Lister"
parent-key="ElementerID" child="ListeElementer"
child-key="ElementID" />
<sql:relationship name="ChecklisteRef" parent="Grupper"
parent-key="GruppeID" child="Checklister"
child-key="CheckListeID" />
<sql:relationship name="ChecklisteItem" parent="Checklister"
parent-key="CheckID" child="ChecklisteElementer"
child-key="ChecklisteElementID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="Soknadsskjema" sql:relation="Soknadsskjemaer">
<xsd:complexType>
<xsd:sequence minOccurs="0" maxOccurs="unbounded">
<xsd:element name="Gruppe" sql:relation="Grupper"
sql:relationship="GruppeRef" minOccurs="0"
maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence minOccurs="1" maxOccurs="unbounded">
<xsd:element name="Liste" sql:relation="Lister"
sql:relationship="GruppeListe" minOccurs="0"
maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ListeElement" sql:relation="ListeElementer"
sql:relationship="ListeElementRef"
minOccurs="1" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="Navn" type="xsd:string" use="required" />
<xsd:attribute name="Tekst" type="xsd:string" use="required" />
<xsd:attribute name="ElementID" type="xsd:unsignedLong"/>
<xsd:attribute name="ID" type="xsd:unsignedLong" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="Navn" type="xsd:string" use="required" />
<xsd:attribute name="Tekst" type="xsd:string" use="required" />
<xsd:attribute name="ListeID" type="xsd:unsignedLong"/>
<xsd:attribute name="ElementerID" type="xsd:unsignedLong" />
<xsd:attribute name="ID" type="xsd:unsignedLong" />
</xsd:complexType>
</xsd:element>
<xsd:element name="Checkliste" sql:relation="Checklister"
sql:relationship="ChecklisteRef" minOccurs="0"
maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ChecklisteElement" sql:relation="ChecklisteElementer"
sql:relationship="ChecklisteItem"
minOccurs="1" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="Navn" type="xsd:string" use="required" />
<xsd:attribute name="Tekst" type="xsd:string" use="required" />
<xsd:attribute name="ChecklisteElementID" type="xsd:unsignedLong"/>
<xsd:attribute name="DefaultMerket" type="xsd:string" />
<xsd:attribute name="ID" type="xsd:unsignedLong" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="Navn" type="xsd:string" use="required" />
<xsd:attribute name="Tekst" type="xsd:string" use="required" />
<xsd:attribute name="CheckListeID" type="xsd:unsignedLong"/>
<xsd:attribute name="CheckID" type="xsd:unsignedLong"/>
<xsd:attribute name="ID" type="xsd:unsignedLong" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="GruppeID" type="xsd:unsignedLong"/>
<xsd:attribute name="SoknadsIDRef" type="xsd:unsignedInt" />
<xsd:attribute name="Gruppenavn" type="xsd:string" />
<xsd:attribute name="ID" type="xsd:unsignedLong" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="ID" type="xsd:long" use="required" />
<xsd:attribute name="UtdanningCV" type="xsd:string" use="required" />
<xsd:attribute name="ErfaringCV" type="xsd:string" use="required" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
It is recieved with this code:
Dim XMLPath As String = savepath + "\" + "Soknadsskjema6.xsd"
Dim XML As System.Xml.XmlDataDocument = New System.Xml.XmlDataDocument
Dim cmd As Microsoft.Data.SqlXml.SqlXmlCommand
Dim leser As System.Xml.XmlReader
cmd = New Microsoft.Data.SqlXml.SqlXmlCommand(dbstreng2)
XML = New System.Xml.XmlDataDocument
cmd.SchemaPath = XMLPath
cmd.CommandType = Microsoft.Data.SqlXml.SqlXmlCommandType.XPath
cmd.CommandText = "Soknadsskjema[@.ID=" & soknadsID & "]"
leser = cmd.ExecuteXmlReader
leser.MoveToContent()
XML.Load(leser)
The database look like this:
CREATE TABLE Soknadsskjemaer
(
ID bigint NOT NULL,
UtdanningCV char(5),
ErfaringCV char(5),
CONSTRAINT Soknadsskjemaer_pk PRIMARY KEY (ID))
insert into Soknadsskjemaer values(0,null,null)
CREATE TABLE SoknadsFiler
(
ID bigint IDENTITY(1,1) NOT NULL,
FilerID bigint NOT NULL,
ExtensionsID bigint NOT NULL unique,
Navn char(60) not null,
Tekst char(150)not null,
MaxSize Int,
CONSTRAINT SoknadsFiler_pk PRIMARY KEY (ID),
CONSTRAINT SoknadsFiler_fk FOREIGN KEY (FilerID)
REFERENCES Soknadsskjemaer (ID)
)
insert into SoknadsFiler values(0,0,'Standard navn','Standard Tekst',null)
CREATE TABLE Extensions
(
ID bigint IDENTITY(1,1) NOT NULL,
ExtensionID bigint NOT NULL,
Type char(4)not null,
CONSTRAINT Extensions_pk PRIMARY KEY (ID),
CONSTRAINT Extensions_fk FOREIGN KEY (ExtensionID)
REFERENCES SoknadsFiler (ExtensionsID)
)
insert into Extensions values (0,'jpeg')
CREATE TABLE Grupper
(
ID bigint IDENTITY(1,1) NOT NULL,
GruppeID bigint NOT NULL unique,
SoknadsIDRef bigint NOT NULL,
Gruppenavn char(40) not null,
CONSTRAINT Grupper_pk PRIMARY KEY (ID),
CONSTRAINT Grupper_fk FOREIGN KEY (SoknadsIDRef)
REFERENCES Soknadsskjemaer (ID)
)
insert into Grupper values (0,0,'Standardgruppe')
CREATE TABLE Lister
(
ID bigint IDENTITY(1,1) NOT NULL,
ListeID bigint NOT NULL,
ElementerID bigint NOT NULL unique,
Navn char(30) not null,
Tekst char(50) not null,
CONSTRAINT Lister_pk PRIMARY KEY (ID),
CONSTRAINT Lister_fk FOREIGN KEY (ListeID)
REFERENCES Grupper (GruppeID)
)
insert into Lister values (0,0,'StandardNavn','StandardFelt')
CREATE TABLE ListeElementer
(
ID bigint IDENTITY(1,1) NOT NULL,
ElementID bigint NOT NULL,
Navn char(30)not null,
Tekst char(30)not null,
CONSTRAINT ListeElementer_pk PRIMARY KEY (ID),
CONSTRAINT ListeElementer_fk FOREIGN KEY (ElementID)
REFERENCES Lister (ElementerID)
)
insert into ListeElementer values (0,'StandardNavn','StandardFelt')
CREATE TABLE Checklister
(
ID bigint IDENTITY(1,1) NOT NULL,
CheckListeID bigint NOT NULL unique,
CheckID bigint NOT NULL,
Navn char(30) not null,
Tekst char(50) not null,
CONSTRAINT Checklister_pk PRIMARY KEY (ID),
CONSTRAINT Checklister_fk FOREIGN KEY (CheckID)
REFERENCES Grupper (GruppeID)
)
insert into Checklister values (0,0,'StandardNavn','StandardFelt')
CREATE TABLE ChecklisteElementer
(
ID bigint IDENTITY(1,1) NOT NULL,
ChecklisteElementID bigint NOT NULL,
Navn char(30) not null,
Tekst char(30) not null,
DefaultMerket char(5) NOT NULL,
CONSTRAINT ChecklisteElementer_pk PRIMARY KEY (ID),
CONSTRAINT ChecklisteElementer_fk FOREIGN KEY (ChecklisteElementID)
REFERENCES Checklister (CheckListeID)
)
insert into ChecklisteElementer values
(0,'StandardNavn','StandardFelt','false')
The values is correct in the databae, but when i receive it it looks like
this:
The xml that is returned.
<Soknadsskjema ID="112" UtdanningCV="True " ErfaringCV="True ">
<Gruppe GruppeID="81" SoknadsIDRef="112" Gruppenavn="Personalia" ID="98">
<Liste Navn="EgenListe1" Tekst="Listen" ListeID="81" ElementerID="13">
<ListeElement Navn="EgenListe1_1" Tekst="Liste1" ElementID="13" />
<ListeElement Navn="EgenListe1_2" Tekst="Liste2" ElementID="13" />
</Liste>
</Gruppe>
</Soknadsskjema>
As you can se the Checkliste element is missing, apparently for no reason.
Can someone pleas help me, i have been struggeled with this for a week now.
Marius Pettersen
..Net developer
MP Productions
It looks like the child-key on the ChecklisteRef relationship is wrong. The
child-key should be CheckID, the Foreign Key to the Gruppe table.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"marius" <marius@.discussions.microsoft.com> wrote in message
news:AF46E927-B3CD-48CC-823F-00C36B7BE00D@.microsoft.com...
> I hvae a problem receiving some xml data from my sql 2000 database and I
> cant figure out why.The information is correct in the database and
receives
> all type of elements except one type.(Even dough this is declare the
same).
> Hope someone can help me with this problem.
>
> Code:
> Here is the xml beeing stored in the database:
> <Soknadsskjema UtdanningCV="True" ErfaringCV="True" ID="112" xmlns="">
> <Gruppe Gruppenavn="Personalia" SoknadsIDRef="112" GruppeID="81">
> <Checkliste Navn="EgenCheck1" Tekst="boxer" CheckID="81"
CheckListeID="10">
> <ChecklisteElement Navn="EgenCheck1_1" Tekst="box1" DefaultMerket="False"
> ChecklisteElementID="10" />
> <ChecklisteElement Navn="EgenCheck1_2" Tekst="box2" DefaultMerket="False"
> ChecklisteElementID="10" />
> </Checkliste>
> <Liste Navn="EgenListe1" Tekst="Listen" ListeID="81" ElementerID="13">
> <ListeElement Navn="EgenListe1_1" Tekst="Liste1" ElementID="13" />
> <ListeElement Navn="EgenListe1_2" Tekst="Liste2" ElementID="13" />
> </Liste>
> </Gruppe></Soknadsskjema>
> This is the xsd schema (stillingsannonse6.xsd)
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship name="GruppeRef" parent="Soknadsskjemaer"
parent-key="ID"
> child="Grupper" child-key="SoknadsIDRef" />
> <sql:relationship name="GruppeListe" parent="Grupper"
parent-key="GruppeID"
> child="Lister" child-key="ListeID" />
> <sql:relationship name="ListeElementRef" parent="Lister"
> parent-key="ElementerID" child="ListeElementer"
> child-key="ElementID" />
> <sql:relationship name="ChecklisteRef" parent="Grupper"
> parent-key="GruppeID" child="Checklister"
> child-key="CheckListeID" />
> <sql:relationship name="ChecklisteItem" parent="Checklister"
> parent-key="CheckID" child="ChecklisteElementer"
> child-key="ChecklisteElementID" />
> </xsd:appinfo>
> </xsd:annotation>
> <xsd:element name="Soknadsskjema" sql:relation="Soknadsskjemaer">
> <xsd:complexType>
> <xsd:sequence minOccurs="0" maxOccurs="unbounded">
> <xsd:element name="Gruppe" sql:relation="Grupper"
> sql:relationship="GruppeRef" minOccurs="0"
> maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence minOccurs="1" maxOccurs="unbounded">
> <xsd:element name="Liste" sql:relation="Lister"
> sql:relationship="GruppeListe" minOccurs="0"
> maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="ListeElement" sql:relation="ListeElementer"
> sql:relationship="ListeElementRef"
> minOccurs="1" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:attribute name="Navn" type="xsd:string" use="required" />
> <xsd:attribute name="Tekst" type="xsd:string" use="required" />
> <xsd:attribute name="ElementID" type="xsd:unsignedLong"/>
> <xsd:attribute name="ID" type="xsd:unsignedLong" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="Navn" type="xsd:string" use="required" />
> <xsd:attribute name="Tekst" type="xsd:string" use="required" />
> <xsd:attribute name="ListeID" type="xsd:unsignedLong"/>
> <xsd:attribute name="ElementerID" type="xsd:unsignedLong" />
> <xsd:attribute name="ID" type="xsd:unsignedLong" />
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="Checkliste" sql:relation="Checklister"
> sql:relationship="ChecklisteRef" minOccurs="0"
> maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="ChecklisteElement" sql:relation="ChecklisteElementer"
> sql:relationship="ChecklisteItem"
> minOccurs="1" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:attribute name="Navn" type="xsd:string" use="required" />
> <xsd:attribute name="Tekst" type="xsd:string" use="required" />
> <xsd:attribute name="ChecklisteElementID" type="xsd:unsignedLong"/>
> <xsd:attribute name="DefaultMerket" type="xsd:string" />
> <xsd:attribute name="ID" type="xsd:unsignedLong" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="Navn" type="xsd:string" use="required" />
> <xsd:attribute name="Tekst" type="xsd:string" use="required" />
> <xsd:attribute name="CheckListeID" type="xsd:unsignedLong"/>
> <xsd:attribute name="CheckID" type="xsd:unsignedLong"/>
> <xsd:attribute name="ID" type="xsd:unsignedLong" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="GruppeID" type="xsd:unsignedLong"/>
> <xsd:attribute name="SoknadsIDRef" type="xsd:unsignedInt" />
> <xsd:attribute name="Gruppenavn" type="xsd:string" />
> <xsd:attribute name="ID" type="xsd:unsignedLong" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="ID" type="xsd:long" use="required" />
> <xsd:attribute name="UtdanningCV" type="xsd:string" use="required" />
> <xsd:attribute name="ErfaringCV" type="xsd:string" use="required" />
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> It is recieved with this code:
> Dim XMLPath As String = savepath + "\" + "Soknadsskjema6.xsd"
> Dim XML As System.Xml.XmlDataDocument = New System.Xml.XmlDataDocument
> Dim cmd As Microsoft.Data.SqlXml.SqlXmlCommand
> Dim leser As System.Xml.XmlReader
> cmd = New Microsoft.Data.SqlXml.SqlXmlCommand(dbstreng2)
> XML = New System.Xml.XmlDataDocument
> cmd.SchemaPath = XMLPath
> cmd.CommandType = Microsoft.Data.SqlXml.SqlXmlCommandType.XPath
> cmd.CommandText = "Soknadsskjema[@.ID=" & soknadsID & "]"
> leser = cmd.ExecuteXmlReader
> leser.MoveToContent()
> XML.Load(leser)
> The database look like this:
> CREATE TABLE Soknadsskjemaer
> (
> ID bigint NOT NULL,
> UtdanningCV char(5),
> ErfaringCV char(5),
> CONSTRAINT Soknadsskjemaer_pk PRIMARY KEY (ID))
> insert into Soknadsskjemaer values(0,null,null)
> CREATE TABLE SoknadsFiler
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> FilerID bigint NOT NULL,
> ExtensionsID bigint NOT NULL unique,
> Navn char(60) not null,
> Tekst char(150)not null,
> MaxSize Int,
> CONSTRAINT SoknadsFiler_pk PRIMARY KEY (ID),
> CONSTRAINT SoknadsFiler_fk FOREIGN KEY (FilerID)
> REFERENCES Soknadsskjemaer (ID)
> )
> insert into SoknadsFiler values(0,0,'Standard navn','Standard
Tekst',null)
> CREATE TABLE Extensions
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> ExtensionID bigint NOT NULL,
> Type char(4)not null,
> CONSTRAINT Extensions_pk PRIMARY KEY (ID),
> CONSTRAINT Extensions_fk FOREIGN KEY (ExtensionID)
> REFERENCES SoknadsFiler (ExtensionsID)
> )
> insert into Extensions values (0,'jpeg')
> CREATE TABLE Grupper
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> GruppeID bigint NOT NULL unique,
> SoknadsIDRef bigint NOT NULL,
> Gruppenavn char(40) not null,
> CONSTRAINT Grupper_pk PRIMARY KEY (ID),
> CONSTRAINT Grupper_fk FOREIGN KEY (SoknadsIDRef)
> REFERENCES Soknadsskjemaer (ID)
> )
> insert into Grupper values (0,0,'Standardgruppe')
> CREATE TABLE Lister
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> ListeID bigint NOT NULL,
> ElementerID bigint NOT NULL unique,
> Navn char(30) not null,
> Tekst char(50) not null,
> CONSTRAINT Lister_pk PRIMARY KEY (ID),
> CONSTRAINT Lister_fk FOREIGN KEY (ListeID)
> REFERENCES Grupper (GruppeID)
> )
> insert into Lister values (0,0,'StandardNavn','StandardFelt')
> CREATE TABLE ListeElementer
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> ElementID bigint NOT NULL,
> Navn char(30)not null,
> Tekst char(30)not null,
> CONSTRAINT ListeElementer_pk PRIMARY KEY (ID),
> CONSTRAINT ListeElementer_fk FOREIGN KEY (ElementID)
> REFERENCES Lister (ElementerID)
> )
> insert into ListeElementer values (0,'StandardNavn','StandardFelt')
> CREATE TABLE Checklister
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> CheckListeID bigint NOT NULL unique,
> CheckID bigint NOT NULL,
> Navn char(30) not null,
> Tekst char(50) not null,
> CONSTRAINT Checklister_pk PRIMARY KEY (ID),
> CONSTRAINT Checklister_fk FOREIGN KEY (CheckID)
> REFERENCES Grupper (GruppeID)
> )
> insert into Checklister values (0,0,'StandardNavn','StandardFelt')
> CREATE TABLE ChecklisteElementer
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> ChecklisteElementID bigint NOT NULL,
> Navn char(30) not null,
> Tekst char(30) not null,
> DefaultMerket char(5) NOT NULL,
> CONSTRAINT ChecklisteElementer_pk PRIMARY KEY (ID),
> CONSTRAINT ChecklisteElementer_fk FOREIGN KEY (ChecklisteElementID)
> REFERENCES Checklister (CheckListeID)
> )
> insert into ChecklisteElementer values
> (0,'StandardNavn','StandardFelt','false')
>
> The values is correct in the databae, but when i receive it it looks like
> this:
> The xml that is returned.
> <Soknadsskjema ID="112" UtdanningCV="True " ErfaringCV="True ">
> <Gruppe GruppeID="81" SoknadsIDRef="112" Gruppenavn="Personalia" ID="98">
> <Liste Navn="EgenListe1" Tekst="Listen" ListeID="81" ElementerID="13">
> <ListeElement Navn="EgenListe1_1" Tekst="Liste1" ElementID="13" />
> <ListeElement Navn="EgenListe1_2" Tekst="Liste2" ElementID="13" />
> </Liste>
> </Gruppe>
> </Soknadsskjema>
> As you can se the Checkliste element is missing, apparently for no reason.
> Can someone pleas help me, i have been struggeled with this for a week
now.
> --
> Marius Pettersen
> .Net developer
> MP Productions
>
|||Oh, my god, thank you.
I can't belive i missed that.
I have litteraly been watching, comparing and testing for days, and haven't
found it.
Again Thank You!
Marius Pettersen
"Bertan ARI [MSFT]" wrote:

> It looks like the child-key on the ChecklisteRef relationship is wrong. The
> child-key should be CheckID, the Foreign Key to the Gruppe table.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "marius" <marius@.discussions.microsoft.com> wrote in message
> news:AF46E927-B3CD-48CC-823F-00C36B7BE00D@.microsoft.com...
> receives
> same).
> CheckListeID="10">
> parent-key="ID"
> parent-key="GruppeID"
> Tekst',null)
> now.
>
>

Friday, March 23, 2012

Problem reciving xml element

now.
Marius Pettersen
.Net developer
MP ProductionsIt looks like the child-key on the ChecklisteRef relationship is wrong. The
child-key should be CheckID, the Foreign Key to the Gruppe table.
--
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"marius" <marius@.discussions.microsoft.com> wrote in message
news:AF46E927-B3CD-48CC-823F-00C36B7BE00D@.microsoft.com...
> I hvae a problem receiving some xml data from my sql 2000 database and I
> cant figure out why.The information is correct in the database and
receives
> all type of elements except one type.(Even dough this is declare the
same).
> Hope someone can help me with this problem.
>
> Code:
> Here is the xml beeing stored in the database:
> <Soknadsskjema UtdanningCV="True" ErfaringCV="True" ID="112" xmlns="">
> <Gruppe Gruppenavn="Personalia" SoknadsIDRef="112" GruppeID="81">
> <Checkliste Navn="EgenCheck1" Tekst="boxer" CheckID="81"
CheckListeID="10">
> <ChecklisteElement Navn="EgenCheck1_1" Tekst="box1" DefaultMerket="False"
> ChecklisteElementID="10" />
> <ChecklisteElement Navn="EgenCheck1_2" Tekst="box2" DefaultMerket="False"
> ChecklisteElementID="10" />
> </Checkliste>
> <Liste Navn="EgenListe1" Tekst="Listen" ListeID="81" ElementerID="13">
> <ListeElement Navn="EgenListe1_1" Tekst="Liste1" ElementID="13" />
> <ListeElement Navn="EgenListe1_2" Tekst="Liste2" ElementID="13" />
> </Liste>
> </Gruppe></Soknadsskjema>
> This is the xsd schema (stillingsannonse6.xsd)
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship name="GruppeRef" parent="Soknadsskjemaer"
parent-key="ID"
> child="Grupper" child-key="SoknadsIDRef" />
> <sql:relationship name="GruppeListe" parent="Grupper"
parent-key="GruppeID"
> child="Lister" child-key="ListeID" />
> <sql:relationship name="ListeElementRef" parent="Lister"
> parent-key="ElementerID" child="ListeElementer"
> child-key="ElementID" />
> <sql:relationship name="ChecklisteRef" parent="Grupper"
> parent-key="GruppeID" child="Checklister"
> child-key="CheckListeID" />
> <sql:relationship name="ChecklisteItem" parent="Checklister"
> parent-key="CheckID" child="ChecklisteElementer"
> child-key="ChecklisteElementID" />
> </xsd:appinfo>
> </xsd:annotation>
> <xsd:element name="Soknadsskjema" sql:relation="Soknadsskjemaer">
> <xsd:complexType>
> <xsd:sequence minOccurs="0" maxOccurs="unbounded">
> <xsd:element name="Gruppe" sql:relation="Grupper"
> sql:relationship="GruppeRef" minOccurs="0"
> maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence minOccurs="1" maxOccurs="unbounded">
> <xsd:element name="Liste" sql:relation="Lister"
> sql:relationship="GruppeListe" minOccurs="0"
> maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="ListeElement" sql:relation="ListeElementer"
> sql:relationship="ListeElementRef"
> minOccurs="1" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:attribute name="Navn" type="xsd:string" use="required" />
> <xsd:attribute name="Tekst" type="xsd:string" use="required" />
> <xsd:attribute name="ElementID" type="xsd:unsignedLong"/>
> <xsd:attribute name="ID" type="xsd:unsignedLong" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="Navn" type="xsd:string" use="required" />
> <xsd:attribute name="Tekst" type="xsd:string" use="required" />
> <xsd:attribute name="ListeID" type="xsd:unsignedLong"/>
> <xsd:attribute name="ElementerID" type="xsd:unsignedLong" />
> <xsd:attribute name="ID" type="xsd:unsignedLong" />
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="Checkliste" sql:relation="Checklister"
> sql:relationship="ChecklisteRef" minOccurs="0"
> maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="ChecklisteElement" sql:relation="ChecklisteElementer"
> sql:relationship="ChecklisteItem"
> minOccurs="1" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:attribute name="Navn" type="xsd:string" use="required" />
> <xsd:attribute name="Tekst" type="xsd:string" use="required" />
> <xsd:attribute name="ChecklisteElementID" type="xsd:unsignedLong"/>
> <xsd:attribute name="DefaultMerket" type="xsd:string" />
> <xsd:attribute name="ID" type="xsd:unsignedLong" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="Navn" type="xsd:string" use="required" />
> <xsd:attribute name="Tekst" type="xsd:string" use="required" />
> <xsd:attribute name="CheckListeID" type="xsd:unsignedLong"/>
> <xsd:attribute name="CheckID" type="xsd:unsignedLong"/>
> <xsd:attribute name="ID" type="xsd:unsignedLong" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="GruppeID" type="xsd:unsignedLong"/>
> <xsd:attribute name="SoknadsIDRef" type="xsd:unsignedInt" />
> <xsd:attribute name="Gruppenavn" type="xsd:string" />
> <xsd:attribute name="ID" type="xsd:unsignedLong" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="ID" type="xsd:long" use="required" />
> <xsd:attribute name="UtdanningCV" type="xsd:string" use="required" />
> <xsd:attribute name="ErfaringCV" type="xsd:string" use="required" />
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> It is recieved with this code:
> Dim XMLPath As String = savepath + "\" + "Soknadsskjema6.xsd"
> Dim XML As System.Xml.XmlDataDocument = New System.Xml.XmlDataDocument
> Dim cmd As Microsoft.Data.SqlXml.SqlXmlCommand
> Dim leser As System.Xml.XmlReader
> cmd = New Microsoft.Data.SqlXml.SqlXmlCommand(dbstreng2)
> XML = New System.Xml.XmlDataDocument
> cmd.SchemaPath = XMLPath
> cmd.CommandType = Microsoft.Data.SqlXml.SqlXmlCommandType.XPath
> cmd.CommandText = "Soknadsskjema[@.ID=" & soknadsID & "]"
> leser = cmd.ExecuteXmlReader
> leser.MoveToContent()
> XML.Load(leser)
> The database look like this:
> CREATE TABLE Soknadsskjemaer
> (
> ID bigint NOT NULL,
> UtdanningCV char(5),
> ErfaringCV char(5),
> CONSTRAINT Soknadsskjemaer_pk PRIMARY KEY (ID))
> insert into Soknadsskjemaer values(0,null,null)
> CREATE TABLE SoknadsFiler
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> FilerID bigint NOT NULL,
> ExtensionsID bigint NOT NULL unique,
> Navn char(60) not null,
> Tekst char(150)not null,
> MaxSize Int,
> CONSTRAINT SoknadsFiler_pk PRIMARY KEY (ID),
> CONSTRAINT SoknadsFiler_fk FOREIGN KEY (FilerID)
> REFERENCES Soknadsskjemaer (ID)
> )
> insert into SoknadsFiler values(0,0,'Standard navn','Standard
Tekst',null)
> CREATE TABLE Extensions
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> ExtensionID bigint NOT NULL,
> Type char(4)not null,
> CONSTRAINT Extensions_pk PRIMARY KEY (ID),
> CONSTRAINT Extensions_fk FOREIGN KEY (ExtensionID)
> REFERENCES SoknadsFiler (ExtensionsID)
> )
> insert into Extensions values (0,'jpeg')
> CREATE TABLE Grupper
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> GruppeID bigint NOT NULL unique,
> SoknadsIDRef bigint NOT NULL,
> Gruppenavn char(40) not null,
> CONSTRAINT Grupper_pk PRIMARY KEY (ID),
> CONSTRAINT Grupper_fk FOREIGN KEY (SoknadsIDRef)
> REFERENCES Soknadsskjemaer (ID)
> )
> insert into Grupper values (0,0,'Standardgruppe')
> CREATE TABLE Lister
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> ListeID bigint NOT NULL,
> ElementerID bigint NOT NULL unique,
> Navn char(30) not null,
> Tekst char(50) not null,
> CONSTRAINT Lister_pk PRIMARY KEY (ID),
> CONSTRAINT Lister_fk FOREIGN KEY (ListeID)
> REFERENCES Grupper (GruppeID)
> )
> insert into Lister values (0,0,'StandardNavn','StandardFelt')
> CREATE TABLE ListeElementer
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> ElementID bigint NOT NULL,
> Navn char(30)not null,
> Tekst char(30)not null,
> CONSTRAINT ListeElementer_pk PRIMARY KEY (ID),
> CONSTRAINT ListeElementer_fk FOREIGN KEY (ElementID)
> REFERENCES Lister (ElementerID)
> )
> insert into ListeElementer values (0,'StandardNavn','StandardFelt')
> CREATE TABLE Checklister
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> CheckListeID bigint NOT NULL unique,
> CheckID bigint NOT NULL,
> Navn char(30) not null,
> Tekst char(50) not null,
> CONSTRAINT Checklister_pk PRIMARY KEY (ID),
> CONSTRAINT Checklister_fk FOREIGN KEY (CheckID)
> REFERENCES Grupper (GruppeID)
> )
> insert into Checklister values (0,0,'StandardNavn','StandardFelt')
> CREATE TABLE ChecklisteElementer
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> ChecklisteElementID bigint NOT NULL,
> Navn char(30) not null,
> Tekst char(30) not null,
> DefaultMerket char(5) NOT NULL,
> CONSTRAINT ChecklisteElementer_pk PRIMARY KEY (ID),
> CONSTRAINT ChecklisteElementer_fk FOREIGN KEY (ChecklisteElementID)
> REFERENCES Checklister (CheckListeID)
> )
> insert into ChecklisteElementer values
> (0,'StandardNavn','StandardFelt','false'
)
>
> The values is correct in the databae, but when i receive it it looks like
> this:
> The xml that is returned.
> <Soknadsskjema ID="112" UtdanningCV="True " ErfaringCV="True ">
> <Gruppe GruppeID="81" SoknadsIDRef="112" Gruppenavn="Personalia" ID="98">
> <Liste Navn="EgenListe1" Tekst="Listen" ListeID="81" ElementerID="13">
> <ListeElement Navn="EgenListe1_1" Tekst="Liste1" ElementID="13" />
> <ListeElement Navn="EgenListe1_2" Tekst="Liste2" ElementID="13" />
> </Liste>
> </Gruppe>
> </Soknadsskjema>
> As you can se the Checkliste element is missing, apparently for no reason.
> Can someone pleas help me, i have been struggeled with this for a w

now.
> --
> Marius Pettersen
> .Net developer
> MP Productions
>|||Oh, my god, thank you.
I can't belive i missed that.
I have litteraly been watching, comparing and testing for days, and haven't
found it.
Again Thank You!
Marius Pettersen
"Bertan ARI [MSFT]" wrote:

> It looks like the child-key on the ChecklisteRef relationship is wrong. Th
e
> child-key should be CheckID, the Foreign Key to the Gruppe table.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "marius" <marius@.discussions.microsoft.com> wrote in message
> news:AF46E927-B3CD-48CC-823F-00C36B7BE00D@.microsoft.com...
> receives
> same).
> CheckListeID="10">
> parent-key="ID"
> parent-key="GruppeID"
> Tekst',null)
> now.
>
>sql

problem queryplan xml template query

Hi,
I use a xsd schema to load XML with a complex structure from a database for
using it in an ASP webpage. Somehow the database uses quite a long time to
make a query plan for the template. The second time the template is used the
database responses quickly, also for other data (using other selection
criteria).
The queryplan is lost when there are no calls for some time, or when a small
change is made to de database stucture, so the next time the ASP page is
called users receive a timeout error.
I have checked all the relevant indexes from the tables that are used for
creating the XML.
Is there any way to influence the speed/persistance of the queryplan that is
created for a xml template query?
Any help will be appreceated,
Albert JanI don't think there is. What is happening is that the first time the query
is compiled and then cached. If you don't run it for a while, the query plan
will be purged from the cache and the query will be recompiled. The only way
to "persist" the plan is to write the FOR XML EXPLICIT mode query inside a
stored proc and call the stored proc. You can use the SQL Profiler to see
what the query is that is being generated.
Best regards
Michael
"Albert Jan" <awonnink@.hotmail.com> wrote in message
news:O$EwS3POFHA.4028@.tk2msftngp13.phx.gbl...
> Hi,
> I use a xsd schema to load XML with a complex structure from a database
> for
> using it in an ASP webpage. Somehow the database uses quite a long time to
> make a query plan for the template. The second time the template is used
> the
> database responses quickly, also for other data (using other selection
> criteria).
> The queryplan is lost when there are no calls for some time, or when a
> small
> change is made to de database stucture, so the next time the ASP page is
> called users receive a timeout error.
> I have checked all the relevant indexes from the tables that are used for
> creating the XML.
> Is there any way to influence the speed/persistance of the queryplan that
> is
> created for a xml template query?
> Any help will be appreceated,
> Albert Jan
>|||Hi Michael,
I had hoped I woudn't have to redesign the solution, because I like the
technique using the template query. But maybe I'll just have to.
Thank you for your answer.
Albert Jan
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:emXkJcUOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> I don't think there is. What is happening is that the first time the query
> is compiled and then cached. If you don't run it for a while, the query
plan
> will be purged from the cache and the query will be recompiled. The only
way
> to "persist" the plan is to write the FOR XML EXPLICIT mode query inside a
> stored proc and call the stored proc. You can use the SQL Profiler to see
> what the query is that is being generated.
> Best regards
> Michael
> "Albert Jan" <awonnink@.hotmail.com> wrote in message
> news:O$EwS3POFHA.4028@.tk2msftngp13.phx.gbl...
to
for
that
>
>

problem queryplan xml template query

Hi,
I use a xsd schema to load XML with a complex structure from a database for
using it in an ASP webpage. Somehow the database uses quite a long time to
make a query plan for the template. The second time the template is used the
database responses quickly, also for other data (using other selection
criteria).
The queryplan is lost when there are no calls for some time, or when a small
change is made to de database stucture, so the next time the ASP page is
called users receive a timeout error.
I have checked all the relevant indexes from the tables that are used for
creating the XML.
Is there any way to influence the speed/persistance of the queryplan that is
created for a xml template query?
Any help will be appreceated,
Albert Jan
I don't think there is. What is happening is that the first time the query
is compiled and then cached. If you don't run it for a while, the query plan
will be purged from the cache and the query will be recompiled. The only way
to "persist" the plan is to write the FOR XML EXPLICIT mode query inside a
stored proc and call the stored proc. You can use the SQL Profiler to see
what the query is that is being generated.
Best regards
Michael
"Albert Jan" <awonnink@.hotmail.com> wrote in message
news:O$EwS3POFHA.4028@.tk2msftngp13.phx.gbl...
> Hi,
> I use a xsd schema to load XML with a complex structure from a database
> for
> using it in an ASP webpage. Somehow the database uses quite a long time to
> make a query plan for the template. The second time the template is used
> the
> database responses quickly, also for other data (using other selection
> criteria).
> The queryplan is lost when there are no calls for some time, or when a
> small
> change is made to de database stucture, so the next time the ASP page is
> called users receive a timeout error.
> I have checked all the relevant indexes from the tables that are used for
> creating the XML.
> Is there any way to influence the speed/persistance of the queryplan that
> is
> created for a xml template query?
> Any help will be appreceated,
> Albert Jan
>
|||Hi Michael,
I had hoped I woudn't have to redesign the solution, because I like the
technique using the template query. But maybe I'll just have to.
Thank you for your answer.
Albert Jan
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:emXkJcUOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> I don't think there is. What is happening is that the first time the query
> is compiled and then cached. If you don't run it for a while, the query
plan
> will be purged from the cache and the query will be recompiled. The only
way[vbcol=seagreen]
> to "persist" the plan is to write the FOR XML EXPLICIT mode query inside a
> stored proc and call the stored proc. You can use the SQL Profiler to see
> what the query is that is being generated.
> Best regards
> Michael
> "Albert Jan" <awonnink@.hotmail.com> wrote in message
> news:O$EwS3POFHA.4028@.tk2msftngp13.phx.gbl...
to[vbcol=seagreen]
for[vbcol=seagreen]
that
>
>
sql

Problem propagating identity fields for multiple tables in SQLXML Bulkload

I am having a problem importing XML documents into SQL using SQLXML Bulkload with an annotated XSD schema. I have three, hierarchical tables in a SQL 2000 DB: tblBatches -> tblRecords -> tblDetails. The XML document I want to import does not contain identity fields. So I am using '.KeepIdentity = False' in my ActiveX VB Script, so SQL Server will generate the key fields for me. When I run the script, the message I get is 'No data was provided for column 'record_ID' on table 'tblDetails', and this column cannot contain NULL values.' So, basically, it's not inserting the matching foreign key generated for tblRecords into tblDetails.
I have been playing around with this for several days, and if I remove all reference to the third table in the hierarchy, tblDetails, from my XSD schema, the data imports and SQL successfully inserts the identity fields for me for the first two tables. If it will insert the identity fields for two tables, why is it having so much trouble doing it for three? I thought SQLXML 3.0 SP2 was supposed to fix the identity propagation problem. I am using SQLXML 3.0 SP2, Windows 2000, VS.NET 2002, and SQL Server 2000.
I am pasting my VB code, XSD schema, and XML document below. I will also attach copies of them, if that is easier.
Here is the VB code I am using to run the bulkload:
Dim BulkLoad As SQLXMLBulkLoad3
Try
'Create the new XML Bulk Load object
BulkLoad = New SQLXMLBulkLoad3()
'Set the connection string.
BulkLoad.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;User ID=mmsuser;Initial Catalog=MMS;Data Source=NDEWITT\NetSDK;"
'Log any errors.
BulkLoad.ErrorLogFile = "C:\TestSQLErrorLog.xml"
'Have it automatically add the identity fields.
BulkLoad.KeepIdentity = False
BulkLoad.CheckConstraints = True
BulkLoad.Transaction = False
'And perform the data import.
BulkLoad.Execute("C:\TestMMSSchemaNETxsd.xsd", _
"C:\TestXDRNoIdentity.xml")
'Clean up.
Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
BulkLoad = Nothing
End Try
Here is my XSD schema:
<?xml version="1.0" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:dt="urn:schemas-microsoft-com:datatypes" xmlns:sql="urn:schemas-microsoft-com:mapping-schema" xmlns:msch="urn:schemas-microsoft-com:mapping-schema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="BatchRec" parent="tblBatches" parent-key="batch_ID" child="tblRecords" child-key="batch_ID" />
<sql:relationship name="RecDetail" parent="tblRecords" parent-key="record_ID" child="tblDetails" child-key="record_ID" />
</xs:appinfo>
</xs:annotation>
<xs:element name="statement_batch" sql:relation="tblBatches" sql:key-fields="batch_ID">
<xs:complexType>
<xs:sequence>
<xs:element name="print_batch_key" type="xs:string" />
<xs:element name="print_batch_type_value" type="xs:string" />
<xs:element name="client_program_key" type="xs:string" />
<xs:element name="print_item_quantity" type="xs:int" />
<xs:element name="start_date" type="xs:date" />
<xs:element name="end_date" type="xs:date" />
<xs:element name="client_key" type="xs:string" />
<xs:element name="client_name" type="xs:string" />
<xs:element name="program_key" type="xs:string" />
<xs:element name="program_name" type="xs:string" />
<xs:element name="return_address_line_1" type="xs:string" />
<xs:element name="return_address_line_2" type="xs:string" />
<xs:element name="return_city" type="xs:string" />
<xs:element name="return_state" type="xs:string" />
<xs:element name="return_zip" type="xs:string" />
<xs:element name="miles_synonym" type="xs:string" />
<xs:element name="statement_synonym" type="xs:string" />
<xs:element name="member_id_synonym" type="xs:string" />
<xs:element name="member_synonym" type="xs:string" />
<xs:element name="redemption_http" type="xs:string" />
<xs:element name="redemption_phone" type="xs:string" />
<xs:element name="print_logo" type="xs:string" />
<xs:element name="run_date" type="xs:date" />
<xs:element name="custom_message" type="xs:string" />
<xs:element name="summary_text" type="xs:string" />
<xs:element name="format_type" type="xs:string" />
<xs:element name="envelope_type" type="xs:string" />
<xs:element name="paper_type" type="xs:string" />
<xs:element name="statement_record" sql:relation="tblRecords" sql:relationship="BatchRec" sql:key-fields="record_ID" minOccurs="1" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="member_id" type="xs:string" />
<xs:element name="first_name" type="xs:string" />
<xs:element name="last_name" type="xs:string" />
<xs:element name="address_line_1" type="xs:string" />
<xs:element name="address_line_2" type="xs:string" />
<xs:element name="city" type="xs:string" />
<xs:element name="state" type="xs:string" />
<xs:element name="zip" type="xs:string" />
<xs:element name="country_id" type="xs:string" />
<xs:element name="employer" type="xs:string" />
<xs:element name="begin_balance" type="xs:int" />
<xs:element name="award_activity" type="xs:int" />
<xs:element name="redeem_activity" type="xs:int" />
<xs:element name="expired_miles" type="xs:int" />
<xs:element name="adjusted_miles" type="xs:int" />
<xs:element name="end_balance" type="xs:int" />
<xs:element name="unvested_balance" type="xs:int" />
<xs:element name="detail_record" sql:relation="tblDetails" sql:relationship="RecDetail" sql:key-fields="detail_ID" minOccurs="1" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="effective_date" type="xs:date" />
<xs:element name="activity_type" type="xs:unsignedByte" />
<xs:element name="activity_description" type="xs:string" />
<xs:element name="activity_miles" type="xs:int" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="MMSSchema" msdata:IsDataSet="true" msdata:EnforceConstraints="False">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element ref="statement_batch" />
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
Here is a sample XML document I want to import:
<statement_batch>
<print_batch_key>609167</print_batch_key>
<print_batch_type_value>Member Statement</print_batch_type_value>
<client_program_key>857148</client_program_key>
<print_item_quantity>108</print_item_quantity>
<start_date>02-13-2004</start_date>
<end_date>03-04-2004</end_date>
<client_key>317229</client_key>
<client_name>CARLSON SYSTEMS</client_name>
<program_key>857143</program_key>
<program_name>Carlson Systems -The Green Mile</program_name>
<return_address_line_1>3475 Piedmont Rd. NE</return_address_line_1>
<return_address_line_2>Suite 300</return_address_line_2>
<return_city>Atlanta</return_city>
<return_state>GA</return_state>
<return_zip>30305</return_zip>
<miles_synonym>Points</miles_synonym>
<statement_synonym>Statement</statement_synonym>
<member_id_synonym>Participant ID</member_id_synonym>
<member_synonym>Participant</member_synonym>
<redemption_http>www.webrewards.com</redemption_http>
<redemption_phone>Merchandise- (877) 690-2080; Travel- (800) 210-8539</redemption_phone>
<print_logo>http://www.webrewards.com/carlson_sy...sonSystems.jpg</print_logo>
<run_date>03-04-2004</run_date>
<custom_message><![CDATA[
<BR>
<BR>]]></custom_message>
<summary_text>
</summary_text>
<format_type>STATEMENT_DETAIL_LOGO</format_type>
<envelope_type>PISTOL_STATEMENT</envelope_type>
<paper_type>STATEMENT_OFFSET_GRAY</paper_type>
<statement_record>
<member_id>859953</member_id>
<first_name>MICHAEL</first_name>
<last_name>STAPLES</last_name>
<address_line_1>4300 DELLWOOD LANE</address_line_1>
<address_line_2>
</address_line_2>
<city>MOUND</city>
<state>MN</state>
<zip>55364</zip>
<country_id>USA</country_id>
<employer>
</employer>
<begin_balance>313152</begin_balance>
<award_activity>21924</award_activity>
<redeem_activity>0</redeem_activity>
<expired_miles>0</expired_miles>
<adjusted_miles>0</adjusted_miles>
<end_balance>335076</end_balance>
<unvested_balance>0</unvested_balance>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>176</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>265</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1344</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>235</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>3103</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>5435</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>154</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>66</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>500</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>597</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1014</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>756</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>56</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>490</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>212</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1875</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>256</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>240</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>202</activity_miles>
</detail_record>
</statement_record>
<statement_record>
<member_id>859954</member_id>
<first_name>M</first_name>
<last_name>LEGAULT</last_name>
<address_line_1>13156 BITTERSWEET ST NW</address_line_1>
<address_line_2>
</address_line_2>
<city>COON RAPIDS</city>
<state>MN</state>
<zip>55448</zip>
<country_id>USA</country_id>
<employer>
</employer>
<begin_balance>91188</begin_balance>
<award_activity>5569</award_activity>
<redeem_activity>0</redeem_activity>
<expired_miles>0</expired_miles>
<adjusted_miles>0</adjusted_miles>
<end_balance>96757</end_balance>
<unvested_balance>0</unvested_balance>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>80</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>105</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>891</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1543</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1475</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1475</activity_miles>
</detail_record>
</statement_record>
</statement_batch>
Any help will be greatly appreciated. Thanks!I discovered your post at Midtown Computer Systems Enterprise web site about SQLXMLBulkLoad and identity columns
in 3 hierarchical tables. I have having similar problems as the ones you described in your post. Were you able to find
a solution? If so, can you share it here? Thanks.sql

Wednesday, March 21, 2012

Problem Ordering XML using ADO.Net

I am converting some legacy ado code to ado.net. This function uses
'for xml explict' and returns the string representation of the xml. I
add a root node (<sales> ) around that string and load into an xml
document. It would return the following:
<sales>
<transaction>
<terms/>
<terms/>
</transaction>
<transaction>
<terms/>
<terms/>
</transaction>
</sales>
My new code looks like this, it uses the exact same SQL query, The
variable 'RootNode' is passed in and is set to 'sales':
Command = New SqlCommand
Command.CommandText = SQL & ", XMLDATA"
Command.CommandType = CommandType.Text
Command.Connection = Connection
Try
xrReader = Command.ExecuteXmlReader()
Try
Dim ds As DataSet = New DataSet
ds.ReadXml(xrReader, XmlReadMode.Fragment)
ds.DataSetName = RootNode.ToString
Dim DataDoc As XmlDataDocument = New
XmlDataDocument(ds)
strReturn = DataDoc.InnerXml
Catch ex As Exception
strReturn = "</" & RootNode & ">"
End Try
Catch ex As Exception
SaveEvent("CommonADO", "Application", ex.Message,
EventLogEntryType.Error, "EMK3Common.dll")
End Try
This function returns strReturn and looks like the following:
<sales>
<transaction/>
<transaction/>
<terms/>
<terms/>
<terms/>
<terms/>
</sales>
As you can see it does not return the same value. I'm hoping someone
can see what I've done wrong and suggest a fix that will return the
same thing as my old ado dll.
Thanks for your help.Can you post the SQL, i.e. whatever 'SQL' is in the
statement below
Command.CommandText = SQL & ", XMLDATA"
Chances are the ORDER BY clause is incorrect or
missing.|||The SQL is posted below. It returns the data in the correct format
when running it through query analyzer and has worked correctly for the
past two years. The problem seems to be in the conversion from dataset
to xml. Thanks for looking at this.
SELECT 1 AS Tag, NULL AS Parent, lc.TransactionID AS
[transaction!1!TransactionID], lc.ContractID AS
[transaction!1!ContractID],
lc.ContractBeginDate AS
[transaction!1!ContractBeginDate], lc.ContractEndDate AS
[transaction!1!ContractEndDate],
lc.TerminationDate AS
[transaction!1!TerminationDate], lc.LeaseID AS [transaction!1!LeaseID],
lc.NetEstimatedMCFsDay AS
[transaction!1!NetEstimatedMCFsDay], lc.BTUValue AS
[transaction!1!BTUValue],
lc.ServiceType AS [transaction!1!ServiceType],
lc.EstimatedVolumeSell AS [transaction!1!EstimatedVolumeSell],
lc.EstimatedVolumeSell2 AS
[transaction!1!EstimatedVolumeSell2], lc.AutomaticExtensionType AS
[transaction!1!AutomaticExtensionType],
lc.AutomaticExtensionInstr AS
[transaction!1!AutomaticExtensionInstr],
lc.ContractInstr AS
[transaction!1!ContractInstr], lc.EFP AS [transaction!1!EFP],
lc.ConfirmationNumber AS
[transaction!1!ConfirmationNumber], lc.AnalysisDate AS
[transaction!1!AnalysisDate],
lc.ConfirmationDate AS
[transaction!1!ConfirmationDate], lc.BuyerOperationsName AS
[transaction!1!BuyerOperationsName],
lc.BuyerOperationsPhone AS
[transaction!1!BuyerOperationsPhone], lc.BuyerOperationsFax AS
[transaction!1!BuyerOperationsFax],
lc.InvoiceVolumeBasedOn AS
[transaction!1!InvoiceVolumeBasedOn], c.ContractType AS
[transaction!1!ContractType],
l.LeaseType AS [transaction!1!LeaseType],
c.LocalBuyerID AS [transaction!1!LocalBuyerID], c.ProducerID AS
[transaction!1!ProducerID],
c.ContractNo AS [transaction!1!ContractNo],
lc.TransactionID AS [terms!2!TransactionID], NULL AS [terms!2!TermsID],
NULL AS [terms!2!BidValue], NULL
AS [terms!2!BidPercent], NULL AS
[terms!2!PriceBasis], NULL AS [terms!2!NYMEXTriggerRights], NULL AS
[terms!2!NYMEXTriggerParam], NULL
AS [terms!2!NYMEXTriggerDeadline], NULL AS
[terms!2!IndexType1], NULL AS [terms!2!IndexPosting1], NULL AS
[terms!2!TermsBeginDate], NULL
AS [terms!2!TermsEndDate], NULL AS
[terms!2!PercentofProceedsType], NULL AS [terms!2!PercentofProceeds],
NULL
AS [terms!2!NYMEXTriggerPrice], NULL AS
[terms!2!NYMEXTriggerOn], NULL AS [terms!2!NYMEXPriceBasis], NULL
AS [terms!2!AOCalculationType], NULL AS
[terms!2!AOPriceScheduleID1], NULL AS [terms!2!AOPriceScheduleID2],
NULL
AS [terms!2!AOPriceScheduleID3], NULL AS
[terms!2!VolumeCriteria], NULL AS [terms!2!VolumeMin], NULL AS
[terms!2!VolumeMax], NULL
AS [terms!2!VolumeUnit]
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
GROUP BY lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo
UNION
SELECT 2, 1, lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo,
lct.TransactionID, lct.TermsID, lct.BidValue,
lct.BidPercent, lct.PriceBasis,
lct.NYMEXTriggerRights, lct.NYMEXTriggerParam,
lct.NYMEXTriggerDeadline, lct.IndexType1, lct.IndexPosting1,
lct.TermsBeginDate, lct.TermsEndDate,
lct.PercentofProceedsType, lct.PercentofProceeds,
lct.NYMEXTriggerPrice, lct.NYMEXTriggerOn,
lct.NYMEXPriceBasis, lct.AOCalculationType,
lct.AOPriceScheduleID1, lct.AOPriceScheduleID2, lct.AOPriceScheduleID3,
lct.VolumeCriteria,
lct.VolumeMin, lct.VolumeMax, lct.VolumeUnit
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID) INNER JOIN
gmm_contracttransactionterms lct ON (lc.SellerID
= lct.SellerID AND lc.ContractID = lct.ContractID AND lc.TransactionID
= lct.TransactionID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
ORDER BY [transaction!1!LeaseID], [transaction!1!TransactionID],
[terms!2!TransactionID], [terms!2!TermsBeginDate]|||The SQL is posted below. It returns the data in the correct format
when running it through query analyzer and has worked correctly for the
past two years. The problem seems to be in the conversion from dataset
to xml. Thanks for looking at this.
SELECT 1 AS Tag, NULL AS Parent, lc.TransactionID AS
[transaction!1!TransactionID], lc.ContractID AS
[transaction!1!ContractID],
lc.ContractBeginDate AS
[transaction!1!ContractBeginDate], lc.ContractEndDate AS
[transaction!1!ContractEndDate],
lc.TerminationDate AS
[transaction!1!TerminationDate], lc.LeaseID AS [transaction!1!LeaseID],
lc.NetEstimatedMCFsDay AS
[transaction!1!NetEstimatedMCFsDay], lc.BTUValue AS
[transaction!1!BTUValue],
lc.ServiceType AS [transaction!1!ServiceType],
lc.EstimatedVolumeSell AS [transaction!1!EstimatedVolumeSell],
lc.EstimatedVolumeSell2 AS
[transaction!1!EstimatedVolumeSell2], lc.AutomaticExtensionType AS
[transaction!1!AutomaticExtensionType],
lc.AutomaticExtensionInstr AS
[transaction!1!AutomaticExtensionInstr],
lc.ContractInstr AS
[transaction!1!ContractInstr], lc.EFP AS [transaction!1!EFP],
lc.ConfirmationNumber AS
[transaction!1!ConfirmationNumber], lc.AnalysisDate AS
[transaction!1!AnalysisDate],
lc.ConfirmationDate AS
[transaction!1!ConfirmationDate], lc.BuyerOperationsName AS
[transaction!1!BuyerOperationsName],
lc.BuyerOperationsPhone AS
[transaction!1!BuyerOperationsPhone], lc.BuyerOperationsFax AS
[transaction!1!BuyerOperationsFax],
lc.InvoiceVolumeBasedOn AS
[transaction!1!InvoiceVolumeBasedOn], c.ContractType AS
[transaction!1!ContractType],
l.LeaseType AS [transaction!1!LeaseType],
c.LocalBuyerID AS [transaction!1!LocalBuyerID], c.ProducerID AS
[transaction!1!ProducerID],
c.ContractNo AS [transaction!1!ContractNo],
lc.TransactionID AS [terms!2!TransactionID], NULL AS [terms!2!TermsID],
NULL AS [terms!2!BidValue], NULL
AS [terms!2!BidPercent], NULL AS
[terms!2!PriceBasis], NULL AS [terms!2!NYMEXTriggerRights], NULL AS
[terms!2!NYMEXTriggerParam], NULL
AS [terms!2!NYMEXTriggerDeadline], NULL AS
[terms!2!IndexType1], NULL AS [terms!2!IndexPosting1], NULL AS
[terms!2!TermsBeginDate], NULL
AS [terms!2!TermsEndDate], NULL AS
[terms!2!PercentofProceedsType], NULL AS [terms!2!PercentofProceeds],
NULL
AS [terms!2!NYMEXTriggerPrice], NULL AS
[terms!2!NYMEXTriggerOn], NULL AS [terms!2!NYMEXPriceBasis], NULL
AS [terms!2!AOCalculationType], NULL AS
[terms!2!AOPriceScheduleID1], NULL AS [terms!2!AOPriceScheduleID2],
NULL
AS [terms!2!AOPriceScheduleID3], NULL AS
[terms!2!VolumeCriteria], NULL AS [terms!2!VolumeMin], NULL AS
[terms!2!VolumeMax], NULL
AS [terms!2!VolumeUnit]
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
GROUP BY lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo
UNION
SELECT 2, 1, lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo,
lct.TransactionID, lct.TermsID, lct.BidValue,
lct.BidPercent, lct.PriceBasis,
lct.NYMEXTriggerRights, lct.NYMEXTriggerParam,
lct.NYMEXTriggerDeadline, lct.IndexType1, lct.IndexPosting1,
lct.TermsBeginDate, lct.TermsEndDate,
lct.PercentofProceedsType, lct.PercentofProceeds,
lct.NYMEXTriggerPrice, lct.NYMEXTriggerOn,
lct.NYMEXPriceBasis, lct.AOCalculationType,
lct.AOPriceScheduleID1, lct.AOPriceScheduleID2, lct.AOPriceScheduleID3,
lct.VolumeCriteria,
lct.VolumeMin, lct.VolumeMax, lct.VolumeUnit
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID) INNER JOIN
gmm_contracttransactionterms lct ON (lc.SellerID
= lct.SellerID AND lc.ContractID = lct.ContractID AND lc.TransactionID
= lct.TransactionID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
ORDER BY [transaction!1!LeaseID], [transaction!1!TransactionID],
[terms!2!TransactionID], [terms!2!TermsBeginDate] FOR XML EXPLICIT|||Your SQL looks okay. Suggest you post to an ADO.NET newsgroup.|||I've had another look at this and think that
problem is that the relationship between transaction
and terms isn't coming through from the query. One
solution is to add it yourself by supplying a new
DataRelation object
After
ds.DataSetName = RootNode.ToString
add this (this is C#, but should translate to VB.NET easily)
DataColumn parentCol =
ds.Tables["transaction"].Columns["TransactionID"];
DataColumn childCol = ds.Tables["terms"].Columns["TransactionID"];
DataRelation TransactionTerms = new DataRelation("TransactionTerms",
parentCol, childCol);
ds.Relations.Add(TransactionTerms);
TransactionTerms.Nested = true;

Tuesday, March 20, 2012

Problem of unicity constraint in a data flow

Hi,

I'm trying to import data from an xml file into a table in my database and I have a problem of unicity constraint in my table.

Here is an example of the data flow in my file :

CITY LOCATION_ID CATALOG_SAISON

PARIS 12 S06

PARIS 12 S07

NEW YORK 15 S06

BERLIN 14 S08

The primary key in my destination table is the two first fields CITY/LOCATION_ID, so I would like to have only one CATALOG_SEASON per CITY/LOCATION_ID. That's why I want insert the following flow in my destination table:

CITY LOCATION_ID CATALOG_SAISON

PARIS 12 S06

NEW YORK 15 S06

BERLIN 14 S08

In Transact-SQL, I can do it easily with an agregation on CATALOG_SAISON field, using a max() function for example : SELECT CITY, LOCATION_ID, max(CATALOG_SAISON) FROM temp_table GROUP BY CITY, LOCATION_ID

In SSIS I would like to do the same with an agregation task but I can't use max() function with a string field. So I can't agregate this field and the only solution I have found now is to create a temporary table in my database to execute the sql request and retrieve data I need.

I'm sure there's a best solution in SSIS, could you help me?

Thanks

Regards

Arnaud Gervais.

Why do you say you can't use Max() function in SSIS against a non-numeric column? What is the error or result?

Did you look at the sort trasformation with the 'Remove rows with duplicate sortvalues' option marked?

|||

I have tried to use Max() function to aggregate my data but I can't do it on a string column. I only have choice between "group by", "count" or "count distinct" aggregation functions.

And I've found this information in msdn : http://msdn2.microsoft.com/en-US/library/ms138031.aspx : "... In contrast to the Transact-SQL MAX function, this operation can be used only with numeric, date, and time data types."

I've tried your solution and it works very fine, thank you very much, you solved my problem!

Arnaud.

Friday, March 9, 2012

Problem loading XML Files

I have been trying unsuccessfully to load a directory full of XML files into SQL Server 2005 Standard. I have not been able to make it work, and I would be grateful for any advice.

A simple version of the files that I am trying to load is TEST.NWS just below. I have removed almost all of the content from the files, leaving as a file TEST.NWS. (Hopefully I have not cut out too much.) This version of the test file leaves me with the same error messages I have with the full file. I have a directory with many such files and each file is much too big to edit manually.

Start TEST.NWS--

<?xml version="1.0" encoding="UTF-8"?>

</NewsComponent>

</NewsML>

<?xml version="1.0" encoding="UTF-8"?>

</NewsComponent>

</NewsML>

END-

The schema I am using is here: http://www.newsml.org/dl.php?fn=NewsML/1.2/specification/NewsML_1.2.xsd

I also tried this schema: http://www.nitf.org/IPTC/NITF/3.3/specification/schema/nitf-3-3.xsd

One version of the steps that I follow are here: http://msdn2.microsoft.com/en-us/library/ms139886.aspx At stage 8 of the "how to" recipe, when I click on the Columns, I get an error. The wording of the error depends on which of the two schemas I use. If I instead click next I also get an error. Clearly I am doing something wrong. Any suggestion for how to fix this will be most welcome. Thanks.

mzfrank,

Please you please copy and paste the error messages you are getting and optional the T-SQL script you are running?

Thanks,

Galex

|||

I was not using a script. I was following the MSDN "How to: Extract Data Using the XML Source" it uses SSIS rather than a T-SQL script. (Is this a task better done using T-SQL rather than SSIS?)

If I use NewsML_1.2.xsd as the schema I get:

Error at Data Flow Task [XML Source [1]]: There was an error setting up the mapping. The 'http://www.w3.org/XML/1998/namespace:lang' attribute is not declared.

If I use nitf-3-3.xsd as the schema I get:

Error at Data Flow Task [XML Source [1]]: The XML Source Adapter does not support mixed content model on Complex Types.

If I ask it to infer a schem I get:

Unable to infer the XSD from the XML file. The XML contains multiple namespaces. (Microsoft Visual Studio)

|||

I am having the same issue. I posted in the SSIS forum, but this is what I wrote there:

I can't import from XML files using a foreach loop. I load an XML file with a generated XSD. When I map the file to the table it has no errors. If I now go back and change to a different XML file, I get an error:

"Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "COLUMNNAME" (129) has lineage ID 2115 that was not previously used in the Data Flow task. Package.dtsx 0 0"

This is for testing purposes. When I run the foreach loop it does not work. Ironically, I do the exact same thing in another foreach loop with a completely different XML and it works fine.

Here is the broken XSD:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="ComputerStatus">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="computer">
<xs:complexType>
<xs:attribute name="GUID" type="xs:string" use="optional" />
<xs:attribute name="WSUSServer" type="xs:string" use="optional" />
<xs:attribute name="WSUSGroup" type="xs:string" use="optional" />
<xs:attribute name="computerName" type="xs:string" use="optional" />
<xs:attribute name="OSBuild" type="xs:unsignedShort" use="optional" />
<xs:attribute name="OSSP" type="xs:unsignedByte" use="optional" />
<xs:attribute name="Model" type="xs:string" use="optional" />
<xs:attribute name="Make" type="xs:string" use="optional" />
<xs:attribute name="BIOS" type="xs:string" use="optional" />
<xs:attribute name="Processor" type="xs:string" use="optional" />
<xs:attribute name="LastReportedStatus" type="xs:string" use="optional" />
<xs:attribute name="LastSyncTime" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Help. Please. What have I done wrong. I imagine there is a flaw in my XML, but I can't pinpoint it.

Here is a sample of the XML file:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<ComputerStatus>
<computerCount QTY="1" />
<computer GUID="edc2b6a5-5d86-467c-8c89-43fa18ae5921" WSUSServer="WSUS" WSUSGroup="THIS" computerName="COMPUTER" OSBuild="3790" OSSP="1" Model="COMPUTERTYPE" Make="HP" BIOS="1" Processor="x86" LastReportedStatus="10/25/2006 12:00:49 PM" LastSyncTime="10/25/2006 11:57:09 AM" />
</ComputerStatus>

Monday, February 20, 2012

Problem inserting XML with DTD

Hi,
I am trying to insert a XML document into a SQL-Server 2005 column of type
XML:
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE ctobject PUBLIC "-//my Ges.m.b.H.//DTD Procedural//EN"
"proced.dtd">
<ctobject>
<content>Some data with entity ref ü</content>
</ctobject>
The C#-Code for the operation is:
SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Insert XmlData(XmlData) Values(@.Col1)";
SqlParameter p1 = cmd.Parameters.Add("@.Col1", SqlDbType.Xml);
XmlTextReader reader = new XmlTextReader(fn);
reader.XmlResolver = resolver;
p2.Value = new SqlXml(reader);
cmd.ExecuteNonQuery();
The operation produces an "InvalidOperationException" - "DTD is not allowed
in XML fragments" on instantiation of the "SqlXml" type.
Removing the DOCTYPE reference eliminates the problem but of course produces
another one: "XmlException" - "Reference to undeclared entity" (caused by
the "ü" entity reference).
What can I do.
Thanks in advance,
ErwinHello Erwin,
Since DTDs are not supported by SQL Server (but XML Schemas are :-) ) you
will have to replace the ü with something else (like <uuml/>, ü or
) and then replace it again on the client side (with ü) when you fetch
it back to the client.
You should however consider not using DTDs.
HTH
/Tobias|||Hello Tobias,
thank you for the response.
Unfortunately not using DTDs is currently not an option (large set of
technical documentation from customer, who is not prepared for switching to
schemas).
So if we decide to work with SQL-Server we will need some kind of
workaround:
usage of partial schemas for indexing (performance) purposes
storage of XML with resolved entities
...
Anyway, a very important requirement is to reproduce an identical copy of
the original document on store/retrieve operations. We will therefore need
some type of replace/restore operations (the way you mentioned in your
response). As an alternative we could try to store a copy of the original
XML to a simple "varchar" column. The first solution has the di

vantage of
producing extra computational overhead. It also ev. manipulates the original
document structure (ev. infuencing search operations). The second solution
produces redundant data with possible problems on data modification
activities.
Kind regards,
Erwin
"Tobias Thernstrm" <ttnospam@.rbam.nospam.se> schrieb im Newsbeitrag
news:ufcBW6pbGHA.4716@.TK2MSFTNGP03.phx.gbl...
> Hello Erwin,
> Since DTDs are not supported by SQL Server (but XML Schemas are :-) ) you
> will have to replace the ü with something else (like <uuml/>, ü
> or ) and then replace it again on the client side (with ü) when you
> fetch it back to the client.
> You should however consider not using DTDs.
> HTH
> /Tobias
>

Problem inserting XML data

Have SP that uses an xml input variable that the SP iterates over,
inserting the attributes in the xml into the db. This all works fine
until I try to insert embedded xml.
Example:
declare @.in xml
set @.in = '<input Manufacturer = "Painkiller1" Dosage = "50" otherstuff
= "<data><field key="ingredients">aspirin</field></data>"/>'
exec #aspirin_insertSP @.input = @.in
...and here's the create statement on the SP:
CREATE PROCEDURE #aspirin_insertSP
@.input xml
AS
DECLARE
@.Manufacturer varchar(50),
@.Dosage int,
@.otherstuff xml
INSERT INTO Aspirin (Manufacturer, Dosage, otherstuff)
select
Manufacturer = T.c.value('@.Manufacturer', 'varchar(50)'),
Dosage = T.c.value('@.Dosage', 'int'),
thestuff = T.c.value('@.thestuff','varchar(5000)')
from @.input.nodes('/input') T(c)
I've tried all manner of escape characters and CDATA combos to no
avail. Does anyone have any ideas/experience with anything like this?
Surely someone else has run into this before.
Any help would be much appreciated
You need to escape out less-than, greater-than and double quotes
inside the 'otherstuff' attribute (the embedded xml).
Also
thestuff = T.c.value('@.thestuff','varchar(5000)')
should be
otherstuff = T.c.value('@.otherstuff ','varchar(5000)')
|||Also you can get SQL Server to escape the attribute
for you by doing this
declare @.innerXML varchar(1000)
set @.innerXML='<data><field key="ingredients">aspirin</field></data>'
declare @.input xml
set @.input = '<input Manufacturer = "Painkiller1" Dosage = "50" />'
set @.input.modify('
insert attribute otherstuff {sql:variable("@.innerXML")}
into (/input)[1]')
|||Hello rbnbigd@.msn.com,

> Have SP that uses an xml input variable that the SP iterates over,
> inserting the attributes in the xml into the db. This all works fine
> until I try to insert embedded xml.
Correct. Literal XML cannot be embedded within an attribute. That's one of
the XML rules in general. You can have embedded XML within a text node, so
something like this should work:
declare @.in xml,@.in2 xml
set @.in = '<input Manufacturer = "Painkiller1" Dosage = "50"><![CDATA[<data><field
key="ingredients">aspirin</field></data>]]></input>'
insert into Aspirin(Manufacturer,Dosage,otherstuff)
select @.in.value('(/input/@.Manufacturer)[1]','varchar(20)'),@.in.value('(/input/@.Dosage)[1]','int'),@.in.value('(/input/text())[1]','varchar(max)')
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Now if this is XML, why would you want to have it in string format anyway
and not just XML?
Best regards
Michael
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7420bc68c8210fb6695960@.news.microsoft.co m...
> Hello rbnbigd@.msn.com,
>
> Correct. Literal XML cannot be embedded within an attribute. That's one of
> the XML rules in general. You can have embedded XML within a text node, so
> something like this should work:
> declare @.in xml,@.in2 xml
> set @.in = '<input Manufacturer = "Painkiller1" Dosage =
> "50"><![CDATA[<data><field
> key="ingredients">aspirin</field></data>]]></input>'
> insert into Aspirin(Manufacturer,Dosage,otherstuff)
> select
> @.in.value('(/input/@.Manufacturer)[1]','varchar(20)'),@.in.value('(/input/@.Dosage)[1]','int'),@.in.value('(/input/text())[1]','varchar(max)')
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>

Friday, March 30, 2012

Problem returning HTMLEncoded text.

Monday, March 26, 2012

Problem reciving xml element

Friday, March 23, 2012

Problem reciving xml element

problem queryplan xml template query

problem queryplan xml template query

Problem propagating identity fields for multiple tables in SQLXML Bulkload

Wednesday, March 21, 2012

Problem Ordering XML using ADO.Net

Tuesday, March 20, 2012

Problem of unicity constraint in a data flow

Friday, March 9, 2012

Problem loading XML Files

Monday, February 20, 2012

Problem inserting XML with DTD

Problem inserting XML data

xp cmdshell

Blog Archive

About Me