How To Turn Imported XML Into A Relational Format in SQL Server 2005..
- 15
- Add a Comment
Welcome back!
I hope you found my how to import XML into SQL Server 2005 helpful. Today I thought we would take a step further and turn that imported XML file into a relational format\table. In my previous post you created a table via T-SQL taht contais a field called xml_data with a data type of XML. In this script I also share with you on how to import an XML file into one field. Once that is done you are now ready to execute the following script to turn it into a table. Lets say that your XML file contains information about a customer and the XML file looks like this:
<Customers>
<Customer>
<id>12345</id>
<city>Los Angeles</city>
<state>CA</state>
<country>USA</country>
<contact>
<firstname>John</firstname>
<lastname>smith</lastname>
</contact>
<dateentered>2007-05-29</dateentered>
</Customer>
</Customers>
Next create a table that you will insert into that matches the field layout in the XML file. Something like this:
Create Table Customer(
id integer null,
city varchar(70) null,
[state] varchar(70) null,
country varchar(75) null,
contact_firstname varchar(30) null,
contact_lastname varchar(40) null,
date_entered datetime null
)
Now with the table created, you can query and insert your XML data into this table. Here is the T-SQL code to accomplish this task:
INSERT INTO [Customer](
[id],
[city],
[state],
[country],
[contact_firstname],
[contact_lastname],
[date_entered]
)
SELECT tab.col.value(‘./id[1]‘,‘varchar(50)’) AS ‘id’,
tab.col.value(‘./city[1]‘,‘varchar(100)’) AS ‘city’,
tab.col.value(‘./state[1]‘,‘varchar(50)’) AS ’state’,
tab.col.value(‘./country[1]‘,‘varchar(100)’) AS ‘country’,
tab.col.value(‘./contact[1]/firstname[1]‘,‘varchar(100)’) AS ‘firstname’,
tab.col.value(‘./contact[1]/lastname[1]‘,‘varchar(100)’) AS ‘lastname’,
tab.col.value(‘./dateentered[1]‘,‘varchar(100)’) AS ‘dateentered’
FROM [XmlImportTest]
CROSS APPLY
xml_data.nodes(‘//Customer’) AS tab(col)
GO
– now return the result set
select * from Customer
I hope you have found this script helpful. If you have any additions to this script please post it here and let us know!
15 Comments
Jim
August 7th, 2008
at 5:24pm
Dude, this was very helpful. Thanks!
Ranga
September 12th, 2008
at 4:40am
sorry this is the shorter version
-
-
-
111241
8
MOHAMAD NASIR
ABAS
Al-Qaida
QI.A.108.03.
2003-09-09T00:00
-
UN List
-
2004-11-26T00:00:00
-
Kaertner Ring 2/2/5/22, 1010 Vienna, Austria
YOUSSEFMNADACOGESELLSCHAFTMBH
2004-11-26T00:00:00
Quirom
November 27th, 2008
at 8:01am
Thank you a lot ! i will try this to solve my problems. xoxo
Andries
January 28th, 2009
at 4:09am
HI
is there any feedback on the issue that Ranga had? I have the exact same issue
rossa veranica
February 9th, 2009
at 1:26am
excellent
Peter Cort Larsen
March 18th, 2009
at 1:23am
If the Customer node has a attribute named ‘id’, how do i get the value of that attrib?
Peter Cort Larsen
March 18th, 2009
at 1:41am
I found out, its like this
tab.col.value(‘./@id‘,‘int’) AS ‘id’
sqlsquirrel
March 18th, 2009
at 8:45pm
Thanks Peter for providing the solution!… I will have to try it out for myself!… Keep posting great solutions!
Hans Brouwer
April 9th, 2009
at 4:20am
Hi Squirrel,
Tnx, it’s helpful in learning about XML and SQL Server. I am wondering though, how to import an XML-file into a relational table with only the XML-file information. In your code you define a table, and you use the absolute values for the attributes, both for the table and for the XML-source.
But consider: everything you need to know is already in the XML-file. I’m thinking about a way to use that info to create a SELECT/INSERT statement for a table, and a virtual table at that. Take the attributes and the data from the XML-file and build/load the relational table from that.
I am sure somebody must have thought about that and build something, either a free script are a professional XML manipulating tool.
Somebody can point me there?
Tnx,
Hans Brouwer
Dave
April 10th, 2009
at 3:44pm
Thanks for the great post. It was really helpful. I used it to grab the non-dynamic data out of an XML file. However, I have a node in my XML file that’s set up like this:
This node goes on at some length (about 50 or so options). I just want the option with the true selected attribute. How could I get that in SQL? Would I have to loop through with a cursor?
Thanks!
Darren
May 5th, 2009
at 9:26am
Any knowledge on how to return the element as well as the node values in this method?
Darren
May 5th, 2009
at 9:57am
Don’t worry about that last question ,
its like this :
tab.col.value(’./@element’,'VARCHAR(2)’) ElementName
I knew it was something to do with prefixing the columns with @ and the tree location but the development I was working with has used its own style of camel case and I kept getting back null results because of the worng cases I typed in the element names.
Andy
June 10th, 2009
at 11:53am
You, my friend, are a genius!!!
Thanks!
A.Rheem
July 1st, 2009
at 5:08am
Thanxxxxxxxxxx
Sushil
October 16th, 2009
at 10:19am
Hey man
thanks a lot for the code.