XML to table - 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

General SQL Server Forums

New to SQL Server Programming

XML to table

Author

Topic

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-08 : 09:43:30

how do i get the xml loaded to table... i want the nodes to be incolumns and not the whole xml fully in a xml data type.
Basically i want to convert a bunch of xml files in to relatioal tables.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 09:46:52

you can use new xml functions like nodes(),query() etc if you're using sql 2005 or above. otherwise you need to use OPENXML. Refer books online for more details

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

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-08 : 10:34:31

yes i did use node to run the query on the xml... but due to the size of the xml it is very very slow.
i want the xml to be loaded to some table (not xml data type fully)column wise like break it into tables and columns and loading

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 10:38:53

if you want XML to be shredded those two are available methods

1. New xml functions or
2.OPENXML

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

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-08 : 10:43:53

can you explain i am new to sql server
for example if i have this xml and the APP note repeats for each record how do i get it on to the table?
<?xml version="1.0" encoding="ISO-8859-1"?>

<Header>
<Company>MOTOR Information Systems</Company>
<SenderName>MOTOR Data Delivery</SenderName>
<SenderPhone>248-312-2700</SenderPhone>
<TransferDate>2010-01-11</TransferDate>
<DocumentTitle>MOTOR_OE_Parts_SST</DocumentTitle>
<EffectiveDate>2010-01-11</EffectiveDate>
<ApprovedFor>SAMPLE</ApprovedFor>
<SubmissionType>SAMPLE</SubmissionType>
<MapperPhoneExt>0</MapperPhoneExt>
<VcdbVersionDate>2009-11-30</VcdbVersionDate>
</Header>
<App action="A" id="1">
<BaseVehicle id="2939"/>
<Qty>0</Qty>
<PartType id="1672"/>
<Position id="22"/>
<Part><![CDATA[NSS]]></Part>
</App>
<Footer>
<RecordCount>1</RecordCount>
</Footer>

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 10:46:06

how is data required for the table? Also whats your root node in above case?

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

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-08 : 10:55:10

for example

App action="A" id="1">
<BaseVehicle id="2939"/>
<Qty>0</Qty>
<PartType id="1672"/>
<Position id="22"/>
<Part><![CDATA[NSS]]></Part>
</App>
this is one record with id, basevehicle id ,qty,partype,position,part as columns

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 11:06:48

[code]
DECLARE @X xml

SELECT @x='<App action="A" id="1">
<BaseVehicle id="2939"/>
<Qty>0</Qty>
<PartType id="1672"/>
<Position id="22"/>
<Part><![CDATA[NSS]]></Part>
</App>
<Footer>
<RecordCount>1</RecordCount>
</Footer>'

SELECT a.b.value('./@id','int') AS id,
a.b.value('./BaseVehicle[1]/@id','int') AS basevehicleid,
a.b.value('./Qty[1]','int') AS Qty,
a.b.value('./PartType[1]/@id','int') AS PartType,
a.b.value('./Position[1]/@id','int') AS Position,
a.b.value('./Part[1]','varchar(100)') AS Part
FROM @x.nodes('/App')a(b)

output
-----------------------------------
id basevehicleid Qty PartType Position Part
1 2939 0 1672 22 NSS

[/code]

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

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-08 : 12:11:44

Thanks it worked for my requirement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 12:19:39

welcome

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

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-08 : 20:53:07

I want to know more about this syntax...
why you have to give the alias like a(b) and not simply a?
Is there any reference for me to read to understand this fully?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-10 : 12:50:09

see

http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources