| laimaj 2005-09-29, 7:24 am |
| I have this weird problem with parent/child relationship. Here's my very
simple xml:
<My>
<Mother>
<Namn>MyName</Namn>
<Child>
<Born>2005-01-02</Born>
</Child>
</Mother>
</My>
And here's the schema:
<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
elementFormDefault="qualified">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="MotherChild"
parent=" XmlDataImport_Mother
" parent-key="ID"
child="XmlDataImport_Child" child-key="MotherID"/>
</xs:appinfo>
</xs:annotation>
<xs:element name="My" id="My" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element name="Mother" minOccurs="0" maxOccurs="unbounded"
sql:relation=" XmlDataImport_Mother
" sql:key-fields="ID">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" minOccurs="0" sql:identity="ignore" sql:field="ID"/>
<xs:element name="Namn" type="xs:string" minOccurs="0" sql:field="Namn"/>
<xs:element name="Child" minOccurs="0" maxOccurs="unbounded"
sql:relationship="MotherChild" sql:relation="XmlDataImport_Child"
sql:key-fields="ID">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" minOccurs="0" sql:identity="ignore"
sql:field="ID"/>
<xs:element name="Born" type="xs:date" sql:datatype="datetime"
minOccurs="0" sql:field="Born"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
I use this code to get xml data into sql server:
SqlXmlCommand cmd = new SqlXmlCommand(connSt
ringBulk);
cmd.RootTag = "My";
cmd.CommandText = "Mother";
cmd.CommandType = SqlXmlCommandType.XPath;
cmd.SchemaPath = ConfigurationSetting
s.AppSettings["schemaFile"];
DataSet dataset = new DataSet();
SqlXmlAdapter adapter = new SqlXmlAdapter(cmd);
XmlTextReader reader = new XmlTextReader(stream
);
dataset.ReadXmlSchema(cmd.SchemaPath);
dataset.ReadXml(reader, XmlReadMode.ReadSchema);
dataset = dataset. GetChanges(DataRowSt
ate.Added);
adapter.Update(dataset);
After execution of this code I get these values in the tables:
-Table XmlDataImport_Mother
:
ID Namn
1 MyName
-Table XmlDataImport_Child
ID MotherID Born
1 null 2005.01.02
FK is on XmlDataImport_Child.MotherID to XmlDataImport_Mother
.ID. Both ID
fields are PK and identity.
Why MotherID is null??? Is something wrong with my schema?
I don't like being of <xs:element name="ID" minOccurs="0"
sql:identity="ignore" sql:field="ID"/> lines in it,
but when I remove them, I get SqlXmlException "Cannot insert explicit value
for identity column in table 'XmlDataImport_Mothe
r' when IDENTITY_INSERT is
set to OFF"
Please, help. It seems so close to working fine...
|