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 w 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