Import question with imbedded tags

Hello,
I'm having a heck of a time trying to make this work, and not being an
expert on XML I'm not even sure what to look for. Can someone point me
in the right direction to import an XML file that contains this:
--snip--
<client>
<Updated>7/1/2006</updated>
<Business Number="11223">
<Name>Smith Glass</name>
<Added>4/2/2005</Added>
<PrimaryContact>
<Name>John Smith</name>
<Phone>301-222-4433</phone>
</PrimaryContact>
<SecondaryContact>
<Name>Jack Jones</name>
<Phone>301-222-4322</phone>
</SecondaryContact>
</Business>
<Business Number="44332">
<Name>Anderson Drywall</name>
<Added>5/1/2005</Added>
<PrimaryContact>
<Name>Mike Anderson</name>
<Phone>301-223-6689</phone>
</PrimaryContact>
<SecondaryContact>
<Name>Nancy Taylor</name>
<Phone>301-542-6643</phone>
</SecondaryContact>
</Business>
</client>
--snip--
Into a SQL table like this:
ID Name Added Person1 Person2
11223 Smith Glass 4/2/2005 John Smith Jack Jones
44332 Anderson Drywall 5/1/2005 Mike Anderson Nancy Taylor
The spaces probably skewed alittle, but hopefully it comes over okay.
Thanks :)
AlexYou can use the nodes() function with CROSS APPLY to shred your xml. Here
is a example cut down from your example:
shred the input file into this table
CREATE TABLE ClientExm
(
ID INT,
Name NVARCHAR(100),
Added DateTime,
Person1 NVARCHAR(100),
Person2 NVARCHAR(100),
)
INSERT INTO ClientExm
SELECT
X.C.value('@.Number', 'INT'),
X.C.value('name[1]', 'NVARCHAR(100)'),
X.C.value('added[1]', 'DATETIME'),
X.C.value('primaryContact[1]/name[1]', 'NVARCHAR(100)'),
X.C.value('secondaryContact[1]/name[1]', 'NVARCHAR(100)')
FROM (SELECT CAST(BulkColumn AS XML) AS B FROM OPENROWSET(
BULK 'C:\Clientexm.xml', SINGLE_BLOB) AS X) AS S
CROSS APPLY
S.B.nodes('/client/business') AS X(C)
SELECT * from ClientExm
ID Name
Added Person1
Person2
-- ---
--
-- ----
---
----
--
11223 Smith Glass
2005-04-02 00:00:00.000 John Smith
Jack Jones
44332 Anderson Drywall
2005-05-01 00:00:00.000 Mike Anderson
Nancy Taylor
11223 Smith Glass
2005-04-02 00:00:00.000 John Smith
Jack Jones
44332 Anderson Drywall
2005-05-01 00:00:00.000 Mike Anderson
Nancy Taylor
the file c:\clientexm.xml contains
<client>
<updated>7/1/2006</updated>
<business Number="11223">
<name>Smith Glass</name>
<added>4/2/2005</added>
<primaryContact>
<name>John Smith</name>
<phone>301-222-4433</phone>
</primaryContact>
<secondaryContact>
<name>Jack Jones</name>
<phone>301-222-4322</phone>
</secondaryContact>
</business>
<business Number="44332">
<name>Anderson Drywall</name>
<added>5/1/2005</added>
<primaryContact>
<name>Mike Anderson</name>
<phone>301-223-6689</phone>
</primaryContact>
<secondaryContact>
<name>Nancy Taylor</name>
<phone>301-542-6643</phone>
</secondaryContact>
</business>
<business Number="11223">
<name>Smith Glass</name>
<added>4/2/2005</added>
<primaryContact>
<name>John Smith</name>
<phone>301-222-4433</phone>
</primaryContact>
<secondaryContact>
<name>Jack Jones</name>
<phone>301-222-4322</phone>
</secondaryContact>
</business>
<business Number="44332">
<name>Anderson Drywall</name>
<added>5/1/2005</added>
<primaryContact>
<name>Mike Anderson</name>
<phone>301-223-6689</phone>
</primaryContact>
<secondaryContact>
<name>Nancy Taylor</name>
<phone>301-542-6643</phone>
</secondaryContact>
</business>
</client>
Dan

> Hello,
> I'm having a heck of a time trying to make this work, and not being an
> expert on XML I'm not even sure what to look for. Can someone point
> me in the right direction to import an XML file that contains this:
> --snip--
> <client>
> <Updated>7/1/2006</updated>
> <Business Number="11223">
> <Name>Smith Glass</name>
> <Added>4/2/2005</Added>
> <PrimaryContact>
> <Name>John Smith</name>
> <Phone>301-222-4433</phone>
> </PrimaryContact>
> <SecondaryContact>
> <Name>Jack Jones</name>
> <Phone>301-222-4322</phone>
> </SecondaryContact>
> </Business>
> <Business Number="44332">
> <Name>Anderson Drywall</name>
> <Added>5/1/2005</Added>
> <PrimaryContact>
> <Name>Mike Anderson</name>
> <Phone>301-223-6689</phone>
> </PrimaryContact>
> <SecondaryContact>
> <Name>Nancy Taylor</name>
> <Phone>301-542-6643</phone>
> </SecondaryContact>
> </Business>
> </client>
> --snip--
> Into a SQL table like this:
> ID Name Added Person1 Person2 11223 Smith
> Glass 4/2/2005 John Smith Jack Jones 44332 Anderson Drywall
> 5/1/2005 Mike Anderson Nancy Taylor
> The spaces probably skewed alittle, but hopefully it comes over okay.
> Thanks :)
> Alex
>|||Dan wrote:
> You can use the nodes() function with CROSS APPLY to shred your xml. Here
> is a example cut down from your example:
> shred the input file into this table
> CREATE TABLE ClientExm
> (
> ID INT,
> Name NVARCHAR(100),
> Added DateTime,
> Person1 NVARCHAR(100),
> Person2 NVARCHAR(100),
> )
>
> INSERT INTO ClientExm
> SELECT
> X.C.value('@.Number', 'INT'),
> X.C.value('name[1]', 'NVARCHAR(100)'),
> X.C.value('added[1]', 'DATETIME'),
> X.C.value('primaryContact[1]/name[1]', 'NVARCHAR(100)'),
> X.C.value('secondaryContact[1]/name[1]', 'NVARCHAR(100)')
> FROM (SELECT CAST(BulkColumn AS XML) AS B FROM OPENROWSET(
> BULK 'C:\Clientexm.xml', SINGLE_BLOB) AS X) AS S
> CROSS APPLY
> S.B.nodes('/client/business') AS X(C)
>
> SELECT * from ClientExm
> ID Name
> Added Person1
> Person2
> -- ---
---
> -- ----
----
> ----
--
> 11223 Smith Glass
> 2005-04-02 00:00:00.000 John Smith
> Jack Jones
> 44332 Anderson Drywall
> 2005-05-01 00:00:00.000 Mike Ander
son
> Nancy Taylor
> 11223 Smith Glass
> 2005-04-02 00:00:00.000 John Smith
> Jack Jones
> 44332 Anderson Drywall
> 2005-05-01 00:00:00.000 Mike Ander
son
> Nancy Taylor
>
Hi Dan,
Too awesome! Thanks for putting this together for me, but I guess I
should've told you I'm using MS SQL 2000... when I try to run this in
query analyzer it gives a syntax error with the BULK command. Hmmm...
if this only works on SQL2000 then I'll hold onto the snippet for when
we upgrade, which no telling when that'll be.
Any suggestions for running this on SQL 2000? If not, i'm back to my
digging :) Thanks,
Alex|||You may be able to do what you need with OPENXML. Below is an example but
look up OPENXML and sp_xml_preparedocument in the BOL for more details.
Also lookup SQLXML and annotated schema for another way to accomlish this.
DECLARE @.hdoc INT
EXEC sp_xml_preparedocument @.hdoc OUTPUT,
'<client>
<updated>7/1/2006</updated>
<business Number="11223">
<name>Smith Glass</name>
<added>4/2/2005</added>
<primaryContact>
<name>John Smith</name>
<phone>301-222-4433</phone>
</primaryContact>
<secondaryContact>
<name>Jack Jones</name>
<phone>301-222-4322</phone>
</secondaryContact>
</business>
<business Number="44332">
<name>Anderson Drywall</name>
<added>5/1/2005</added>
<primaryContact>
<name>Mike Anderson</name>
<phone>301-223-6689</phone>
</primaryContact>
<secondaryContact>
<name>Nancy Taylor</name>
<phone>301-542-6643</phone>
</secondaryContact>
</business>
<business Number="11223">
<name>Smith Glass</name>
<added>4/2/2005</added>
<primaryContact>
<name>John Smith</name>
<phone>301-222-4433</phone>
</primaryContact>
<secondaryContact>
<name>Jack Jones</name>
<phone>301-222-4322</phone>
</secondaryContact>
</business>
<business Number="44332">
<name>Anderson Drywall</name>
<added>5/1/2005</added>
<primaryContact>
<name>Mike Anderson</name>
<phone>301-223-6689</phone>
</primaryContact>
<secondaryContact>
<name>Nancy Taylor</name>
<phone>301-542-6643</phone>
</secondaryContact>
</business>
</client>'
INSERT INTO ClientExm
SELECT *
FROM OPENXML(@.hdoc, '/client/business', 2)
WITH
([Number] INT '@.Number',
[name] NVARCHAR(100) 'name',
[added] DATETIME 'added',
[primaryContact] NVARCHAR(100) 'primaryContact/name',
[secondaryContact] NVARCHAR(100)'secondaryContact/name'
)
Dan
EXEC sp_xml_removedocument @.hdoc

> Dan wrote:
>
> Hi Dan,
> Too awesome! Thanks for putting this together for me, but I guess I
> should've told you I'm using MS SQL 2000... when I try to run this in
> query analyzer it gives a syntax error with the BULK command. Hmmm...
> if this only works on SQL2000 then I'll hold onto the snippet for when
> we upgrade, which no telling when that'll be.
> Any suggestions for running this on SQL 2000? If not, i'm back to my
> digging :) Thanks,
> Alex
>sql