reading multiple xml files - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

reading multiple xml files

Author

Topic

PravinKBandi
Starting Member

4 Posts

Posted - 2011-08-24 : 05:28:30

Hi all,

I am new to T-SQL, I am trying to retrieve data from XML file into a SQL database. I am providing some part of the xml file format having loop node. When i am using the T-SQL code, it is appending the multiple values into the same column name:

xml file:

<ns0:X12_00401_850 xmlns:ns0="http://schemas.microsoft.com...">
<ST>
<ST01>850</ST01>
</ST>
<ns0:BEG>
<BEG01>00</BEG01>
<BEG02>SA</BEG02>
</ns0:BEG>
<ns0:TD5>
<TD501>O</TD501>
</ns0:TD5>
<ns0:N1Loop1>
<ns0:N1>
<N101>BY</N101>
<N104>FO</N104>
</ns0:N1>
</ns0:N1Loop1>
<ns0:N1Loop1>
<ns0:N1>
<N101>ST</N101>
<N104>PW</N104>
</ns0:N1>
</ns0:N1Loop1>
.......
.......
</ns0:X12_00401_850>

TSQL Code:

INSERT INTO dbo.EDI_DATA_4M_XML ([ST01],[BEG02],[BEG03],[BEG05],[N102],
[N104],[N301],[N302],[PO101],[PO103],[PO104],[PO105],[PO106],[PO108],
[SCH01],[SCH06],[TD501],[TD503])
SELECT X.ST.query('ST01').value('.', 'VARCHAR(30)'),
X.ST.query('//BEG02').value('.', 'VARCHAR(30)'),
X.ST.query('//BEG03').value('.', 'VARCHAR(30)'),
X.ST.query('//BEG05').value('.', 'VARCHAR(30)'),
X.ST.query('//N102').value('.', 'VARCHAR(30)'),
X.ST.query('//N104').value('.', 'VARCHAR(30)'),

-- here having issue with N104 node, that is retrieving FO and PW

X.ST.query('//N301').value('.', 'VARCHAR(30)'),
X.ST.query('//N302').value('.', 'VARCHAR(30)'),
X.ST.query('//PO101').value('.', 'VARCHAR(30)'),
X.ST.query('//PO103').value('.', 'VARCHAR(30)'),
X.ST.query('//PO104').value('.', 'VARCHAR(30)'),
X.ST.query('//PO105').value('.', 'VARCHAR(30)'),
X.ST.query('//PO106').value('.', 'VARCHAR(30)'),
X.ST.query('//PO108').value('.', 'VARCHAR(30)'),
X.ST.query('//SCH01').value('.', 'VARCHAR(30)'),
X.ST.query('//SCH06').value('.', 'VARCHAR(30)'),
X.ST.query('//TD501').value('.', 'VARCHAR(30)'),
X.ST.query('//TD503').value('.', 'VARCHAR(30)')
FROM (
SELECT CAST(x AS XML)
FROM OPENROWSET(
BULK 'C:\edi1.xml',
SINGLE_BLOB) AS T(x)
) AS T(x)
CROSS APPLY x.nodes('//ST') AS X(ST);

Please any one suggest me how I can overcome this issue. And also let me know is there any way I can load multiple XML files into SQL table.

Thanks in Advance,

-Dave-

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 07:42:54

see example here


declare @x xml
set @x='<ns0:X12_00401_850 xmlns:ns0="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions">
<ST>
<ST01>850</ST01>
</ST>
<ns0:BEG>
<BEG01>00</BEG01>
<BEG02>SA</BEG02>
</ns0:BEG>
<ns0:TD5>
<TD501>O</TD501>
</ns0:TD5>
<ns0:N1Loop1>
<ns0:N1>
<N101>BY</N101>
<N104>FO</N104>
</ns0:N1>
</ns0:N1Loop1>
<ns0:N1Loop1>
<ns0:N1>
<N101>ST</N101>
<N104>PW</N104>
</ns0:N1>
</ns0:N1Loop1>
</ns0:X12_00401_850>'



SELECT t.u.value('./ST[1]/ST01[1]','int'),
t.u.value('declare namespace ns0="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";./ns0:BEG[1]/BEG01[1]','varchar(10)'),
t.u.value('declare namespace ns0="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";./ns0:BEG[1]/BEG02[1]','varchar(10)'),
t.u.value('declare namespace ns0="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";./ns0:TD5[1]/TD501[1]','varchar(10)'),
p.q.value('declare namespace ns0="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";./ns0:N1[1]/N101[1]','varchar(100)'),
p.q.value('declare namespace ns0="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";./ns0:N1[1]/N104[1]','varchar(100)')
FROM @x.nodes('declare namespace ns0="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";/ns0:X12_00401_850')t(u)
CROSS APPLY t.u.nodes('declare namespace ns0="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";./ns0:N1Loop1')p(q)


output
---------------------------------------------------
850	00	SA	O	BY	FO
850	00	SA	O	ST	PW

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

PravinKBandi
Starting Member

4 Posts

Posted - 2011-09-01 : 06:09:11

Hi,

Thanks for your reply. Your code will answer all my questions.
You saved my day...

PravinKBandi
Starting Member

4 Posts

Posted - 2011-09-01 : 07:12:38

Hi again,

can u let me know how can i work with this code by passing an XML file as input instead of hardcoding the content of the xml file here...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-01 : 07:44:48

you need to use temporary table for that. use OPENROWSET BULK as shown below to populate table

http://msdn.microsoft.com/en-us/library/ms191184.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

PravinKBandi
Starting Member

4 Posts

Posted - 2011-09-01 : 10:46:56

Hi,

Ya I got it using the following code...Thanks a lot man for your quick replies..

declare @x xml;
select @x = convert(xml, x.BulkColumn)
from openrowset(bulk N'c:\sample2.xml', single_blob) as x;

and from here I am implementing the code that u have provided...

Thanks & Regards,
Dave

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources