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!