MS SQL XML - SQLXMLBulkLoad Question

Author

SQLXMLBulkLoad Question

Tom Bean

2005-07-28, 11:36 am

I'm using SQLXMLBulkLoad to import an XML document into SQL Server. The XML
document has an hierarchy of elements that get imported into different
tables but the relationship of the child elements to the parent element is
lost. I have included part of the XML & XSD document below.

The 'uid' elements are unique id of the various types of elements. The
Person and Address data are imported to their corresponding tables but the
'uid' of the Person record is not added to the Address table so there is no
indication of which address belongs to which person.

Is there any thing I can do to use SQLXMLBulkLoad and perserve the
relationships?

Thanks,
Tom
--------------
The following is part of the XML:

<?xml version="1.0" standalone="yes"?>
<PersonList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://tempuri.org/PersonList.xsd">
<Person>
<uid>416</uid>
<firstName>Tom</firstName>
<lastName>Bean</lastName>
<Type>Individual</Type>
<programList>
<program>Program1</program>
</programList>
<addressList>
<address>
<uid>242</uid>
<address1>123 E Main Street</address1>
<city>Dallas, Texas</city>
<country>USA</country>
</address>
<address>
<uid>243</uid>
<address1>23r Commerce Street</address1>
<city>Dallas, Texas</city>
<country>USA</country>
</address>
</addressList>
</Person>
</PersonList>

The corresponding XSD document is:

<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="PersonList"
targetNamespace="http://tempuri.org/PersonList.xsd"
xmlns:mstns="http://tempuri.org/PersonList.xsd"
xmlns="http://tempuri.org/PersonList.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
attributeFormDefault
="qualified" elementFormDefault="qualified">
<xs:element name="PersonList" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Person">
<xs:complexType>
<xs:sequence>
<xs:element name="uid" type="xs:integer"
minOccurs="0" />
<xs:element name="lastName" type="xs:string"
minOccurs="0" />
<xs:element name="Type" type="xs:string"
minOccurs="0" />
<xs:element name="firstName" type="xs:string"
minOccurs="0" />
<xs:element name="title" type="xs:string"
minOccurs="0" />
<xs:element name="remarks" type="xs:string"
minOccurs="0" />
<xs:element name="programList" minOccurs="0"
maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="program"
nillable="true" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:simpleContent
msdata:ColumnName="program_Text" msdata:Ordinal="0">
<xs:extension
base="xs:string">
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="addressList" minOccurs="0"
maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="address"
minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="uid"
type="xs:integer" minOccurs="0" />
<xs:element
name="address1" type="xs:string" minOccurs="0" />
<xs:element name="city"
type="xs:string" minOccurs="0" />
<xs:element
name="country" type="xs:string" minOccurs="0" />
<xs:element
name="postalCode" type="xs:string" minOccurs="0" />
<xs:element
name="address2" type="xs:string" minOccurs="0" />
<xs:element
name="stateOrProvince" type="xs:string" minOccurs="0" />
<xs:element
name="address3" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>

Peter Yang [MSFT]

2005-07-29, 3:24 am

Hello Tom,

You may consider to use sql:relationship annotation in the schema. For
example:

xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="CustCustOrder"
parent="Cust"
parent-key="CustomerID"
child="CustOrder"
child-key="CustomerID" />
</xsd:appinfo>
</xsd:annotation>

Please refer to BOL of SQL XML 3.0 for more details.

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

====================
====================
=============

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: "Tom Bean" <tbean@newsgroup.nospam>
| Subject: SQLXMLBulkLoad Question
| Date: Thu, 28 Jul 2005 10:51:42 -0500
| Lines: 131
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <uH7dLx4kFHA.2916@TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.xml
| NNTP-Posting-Host: 71.4.140.141.ptr.us.xo.net 71.4.140.141
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.xml:3913
| X-Tomcat-NG: microsoft.public.sqlserver.xml
|
| I'm using SQLXMLBulkLoad to import an XML document into SQL Server. The
XML
| document has an hierarchy of elements that get imported into different
| tables but the relationship of the child elements to the parent element
is
| lost. I have included part of the XML & XSD document below.
|
| The 'uid' elements are unique id of the various types of elements. The
| Person and Address data are imported to their corresponding tables but
the
| 'uid' of the Person record is not added to the Address table so there is
no
| indication of which address belongs to which person.
|
| Is there any thing I can do to use SQLXMLBulkLoad and perserve the
| relationships?
|
| Thanks,
| Tom
| --------------
| The following is part of the XML:
|
| <?xml version="1.0" standalone="yes"?>
| <PersonList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
| xmlns="http://tempuri.org/PersonList.xsd">
| <Person>
| <uid>416</uid>
| <firstName>Tom</firstName>
| <lastName>Bean</lastName>
| <Type>Individual</Type>
| <programList>
| <program>Program1</program>
| </programList>
| <addressList>
| <address>
| <uid>242</uid>
| <address1>123 E Main Street</address1>
| <city>Dallas, Texas</city>
| <country>USA</country>
| </address>
| <address>
| <uid>243</uid>
| <address1>23r Commerce Street</address1>
| <city>Dallas, Texas</city>
| <country>USA</country>
| </address>
| </addressList>
| </Person>
| </PersonList>
|
| The corresponding XSD document is:
|
| <?xml version="1.0" encoding="utf-8"?>
| <xs:schema id="PersonList"
| targetNamespace="http://tempuri.org/PersonList.xsd"
| xmlns:mstns="http://tempuri.org/PersonList.xsd"
| xmlns="http://tempuri.org/PersonList.xsd"
| xmlns:xs="http://www.w3.org/2001/XMLSchema"
| xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
| attributeFormDefault
="qualified" elementFormDefault="qualified">
| <xs:element name="PersonList" msdata:IsDataSet="true">
| <xs:complexType>
| <xs:choice maxOccurs="unbounded">
| <xs:element name="Person">
| <xs:complexType>
| <xs:sequence>
| <xs:element name="uid" type="xs:integer"
| minOccurs="0" />
| <xs:element name="lastName" type="xs:string"
| minOccurs="0" />
| <xs:element name="Type" type="xs:string"
| minOccurs="0" />
| <xs:element name="firstName" type="xs:string"
| minOccurs="0" />
| <xs:element name="title" type="xs:string"
| minOccurs="0" />
| <xs:element name="remarks" type="xs:string"
| minOccurs="0" />
| <xs:element name="programList" minOccurs="0"
| maxOccurs="unbounded">
| <xs:complexType>
| <xs:sequence>
| <xs:element name="program"
| nillable="true" minOccurs="0" maxOccurs="unbounded">
| <xs:complexType>
| <xs:simpleContent
| msdata:ColumnName="program_Text" msdata:Ordinal="0">
| <xs:extension
| base="xs:string">
| </xs:extension>
| </xs:simpleContent>
| </xs:complexType>
| </xs:element>
| </xs:sequence>
| </xs:complexType>
| </xs:element>
| <xs:element name="addressList" minOccurs="0"
| maxOccurs="unbounded">
| <xs:complexType>
| <xs:sequence>
| <xs:element name="address"
| minOccurs="0" maxOccurs="unbounded">
| <xs:complexType>
| <xs:sequence>
| <xs:element
name="uid"
| type="xs:integer" minOccurs="0" />
| <xs:element
| name="address1" type="xs:string" minOccurs="0" />
| <xs:element
name="city"
| type="xs:string" minOccurs="0" />
| <xs:element
| name="country" type="xs:string" minOccurs="0" />
| <xs:element
| name="postalCode" type="xs:string" minOccurs="0" />
| <xs:element
| name="address2" type="xs:string" minOccurs="0" />
| <xs:element
| name="stateOrProvince" type="xs:string" minOccurs="0" />
| <xs:element
| name="address3" type="xs:string" minOccurs="0" />
| </xs:sequence>
| </xs:complexType>
| </xs:element>
| </xs:sequence>
| </xs:complexType>
| </xs:element>
| </xs:sequence>
| </xs:complexType>
| </xs:element>
| </xs:choice>
| </xs:complexType>
| </xs:element>
| </xs:schema>
|
|
|

Sponsored Links