XML Bulk Load - XSD to handle nested nodes

Question

Post reply

XML Bulk Load - XSD to handle nested nodes

register 97758

SSC Rookie

Points: 44
More actions
March 21, 2010 at 3:16 pm

#96638

How would I insert a file that was structured like this into one table?
I can get the children of location into the table but I can't figure out the schema structure to get the children of the address node to insert.
<?xml version="1.0" encoding="UTF-8" ?>
<locations>
<location>
<id>11111</id>
<name>Business Name</name>
<address>
<street>111 Street Address</street>
<delivery_point/>
<city>City Name</city>
<state>ST</state>
<postal_code>44444</postal_code>
<cross_street/>
<latitude>33.00</latitude>
<longitude>-100.00</longitude>
</address>
</location>
</locations>

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Would the following code snippet help you?

DECLARE @xml XML

SET @xml=

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

<locations>

<location>

<id>11111</id>

<name>Business Name</name>

<address>

<street>111 Street Address</street>

<delivery_point/>

<city>City Name</city>

<state>ST</state>

<postal_code>44444</postal_code>

<cross_street/>

<latitude>33.00</latitude>

<longitude>-100.00</longitude>

</address>

</location>

</locations>'

SELECT @xml

SELECT

c.value('id[1]','int') AS id,

c.value('name[1]','varchar(30)') AS name,

v.value('street[1]','varchar(30)') AS street,

v.value('city[1]','varchar(30)') AS city

FROM @xml.nodes('locations/location') T(c)

CROSS APPLY

t.c.nodes('address') U(v)

/* result set

idnamestreetcity

11111Business Name111 Street AddressCity Name

*/

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

register 97758 SSC Rookie Points: 44 More actions · Answer 2

Thanks, but I was talking about how to make the XSD for use with XML Bulk Load, hence the title of the thread " XML Bulk Load - XSD to handle nested nodes"

LutzM SSC Guru Points: 107049 More actions · Answer 3

register 97758 (3/21/2010)
Thanks, but I was talking about how to make the XSD for use with XML Bulk Load, hence the title of the thread " XML Bulk Load - XSD to handle nested nodes"

And I was referring to your explanation within the thread:

How would I insert a file that was structured like this into one table?

Right now, I'm not sure what you're looking for...

Do you need help to create the XDR file?

If so, please have a look at http://support.microsoft.com/kb/316005/en-us.

You also might consider using an XML View Mapper: http://www.microsoft.com/downloads/details.aspx?FamilyId=843D1DBA-EC44-4218-A18D-2528AA5127CF&displaylang=en

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

register 97758 SSC Rookie Points: 44 More actions · Answer 4

I'm trying to figure out how to structure the XSD file for use with XML Bulk Load getting the values of the children of the root node in the same table as the children of the children of the root node.

So, I would like to get the above XML nodes into one table with the columns listed below using XML Bulk Load.

id

name

street

delivery_point

city

state

postal_code

cross_street

latitude

longitude

register 97758 SSC Rookie Points: 44 More actions · Answer 5

Thanks for your response.

The XML view mapper doesn't appear to translate an XML file into and XDR and the Microsoft support page you gave works as long as you don't have a nested node like the "address" node in the example above. I can get the first level nodes to load but not the address node. For example, I can get the XDR below to work but can't figure out how to add to the schema to account for the nodes inside the address node.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xsd:element name="location" sql:relation="listings" >

<xsd:complexType>

<xsd:sequence>

<xsd:element name="id"

sql:field="id"

type="xsd:integer" />

</xsd:complexType>

</xsd:element>

</xsd:schema>