In this article, I will explain you how to parse XML or import XML to SQL Server Database Table.
Here are the XML File:
<?xml version="1.0"?>
<Orders>
<Order OrderNumber="99503" OrderDate="2013-10-20">
<Address Type="Shipping">
<Name>Ellen Adams</Name>
<Street>123 Maple Street</Street>
<City>Mill Valley</City>
<State>CA</State>
<Zip>10999</Zip>
<Country>USA</Country>
</Address>
<Address Type="Billing">
<Name>Tai Yee</Name>
<Street>8 Oak Avenue</Street>
<City>Old Town</City>
<State>PA</State>
<Zip>95819</Zip>
<Country>USA</Country>
</Address>
<DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes>
<Items>
<Item PartNumber="872-AA">
<ProductName>Lawnmower</ProductName>
<Quantity>1</Quantity>
<USPrice>148.95</USPrice>
<Comment>Confirm this is electric</Comment>
</Item>
<Item PartNumber="926-AA">
<ProductName>Baby Monitor</ProductName>
<Quantity>2</Quantity>
<USPrice>39.98</USPrice>
<ShipDate>2013-05-21</ShipDate>
</Item>
</Items>
</Order>
</Orders>
To Query XML File, you need to store in to xml variable @MyXML:
DECLARE @MyXML XML
SET @MyXML = '<?xml version="1.0"?>
<Orders>
<Order OrderNumber="99503" OrderDate="2013-10-20">
<Address Type="Shipping">
<Name>....</Name>
<Street>123 Maple Street</Street>
<City>Mill Valley</City>
<State>CA</State>
<Zip>10999</Zip>
<Country>...</Country>
</Address>
<Address Type="Billing">
<Name>Tai Yee</Name>
<Street>8 Oak Avenue</Street>
<City>Old Town</City>
<State>PA</State>
<Zip>95819</Zip>
<Country>...</Country>
</Address>
<DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes>
<Items>
<Item PartNumber="872-AA">
<ProductName>Lawnmower</ProductName>
<Quantity>1</Quantity>
<USPrice>148.95</USPrice>
<Comment>Confirm this is electric</Comment>
</Item>
<Item PartNumber="926-AA">
<ProductName>Baby Monitor</ProductName>
<Quantity>2</Quantity>
<USPrice>39.98</USPrice>
<ShipDate>2013-05-21</ShipDate>
</Item>
</Items>
</Order>
</Orders>
Now, you use SQL query to get Order header from @MyXML:
SELECT Col.value('@OrderNumber', 'int') AS 'Order',
Col.value('@OrderDate', 'date') AS 'OrderDate',
Col.value('(Address[@Type=''Shipping'']/Name/text())[1]', 'nvarchar(50)') AS 'Shipping_Name',
Col.value('(Address[@Type=''Shipping'']/Street/text())[1]', 'nvarchar(50)') AS 'Shipping_Street',
Col.value('(Address[@Type=''Shipping'']/City/text())[1]', 'nvarchar(50)') AS 'Shipping_City',
Col.value('(Address[@Type=''Shipping'']/State/text())[1]', 'nvarchar(50)') AS 'Shipping_State',
Col.value('(Address[@Type=''Shipping'']/Zip/text())[1]', 'nvarchar(50)') AS 'Shipping_Zip',
Col.value('(Address[@Type=''Shipping'']/Country/text())[1]', 'nvarchar(50)') AS 'Shipping_Country',
Col.value('(Address[@Type=''Billing'']/Name/text())[1]', 'nvarchar(50)') AS 'Billing_Name',
Col.value('(Address[@Type=''Billing'']/Street/text())[1]', 'nvarchar(50)') AS 'Billing_Street',
Col.value('(Address[@Type=''Billing'']/City/text())[1]', 'nvarchar(50)') AS 'Billing_City',
Col.value('(Address[@Type=''Billing'']/State/text())[1]', 'nvarchar(50)') AS 'Billing_State',
Col.value('(Address[@Type=''Billing'']/Zip/text())[1]', 'nvarchar(50)') AS 'Billing_Zip',
Col.value('(Address[@Type=''Billing'']/Country/text())[1]', 'nvarchar(50)') AS 'Billing_Country',
Col.value('(DeliveryNotes/text())[1]', 'nvarchar(250)') AS 'DeliveryNotes'
FROM @MyXML.nodes('/Orders/Order') AS T(Col)
Then, you can use SQL query to get Order Items from @MyXML:
SELECT
Col.value('(../../../Order/@OrderNumber)[1]', 'int') AS 'Order',
Col.value('(@PartNumber)[1]', 'nvarchar(50)') AS 'PartNumber' ,
Col.value('(ProductName/text())[1]', 'nvarchar(250)') AS 'ProductName' ,
Col.value('(Quantity/text())[1]', 'int') AS 'Quantity',
Col.value('(USPrice/text())[1]', 'float') AS 'USPrice',
Col.value('(Comment/text())[1]', 'nvarchar(250)') AS 'Comment',
Col.value('(ShipDate/text())[1]', 'date') AS 'ShipDate'
FROM @MyXML.nodes('/Orders/Order/Items/Item') AS T(Col)
To make it easier, here's the full code:
DECLARE @MyXML XML
SET @MyXML = '<?xml version="1.0"?>
<Orders>
<Order OrderNumber="99503" OrderDate="2013-10-20">
<Address Type="Shipping">
<Name>...</Name>
<Street>123 Maple Street</Street>
<City>Mill Valley</City>
<State>CA</State>
<Zip>10999</Zip>
<Country>...</Country>
</Address>
<Address Type="Billing">
<Name>Tai Yee</Name>
<Street>8 Oak Avenue</Street>
<City>Old Town</City>
<State>PA</State>
<Zip>95819</Zip>
<Country>...</Country>
</Address>
<DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes>
<Items>
<Item PartNumber="872-AA">
<ProductName>Lawnmower</ProductName>
<Quantity>1</Quantity>
<USPrice>148.95</USPrice>
<Comment>Confirm this is electric</Comment>
</Item>
<Item PartNumber="926-AA">
<ProductName>Baby Monitor</ProductName>
<Quantity>2</Quantity>
<USPrice>39.98</USPrice>
<ShipDate>2013-05-21</ShipDate>
</Item>
</Items>
</Order>
</Orders>'
SELECT
Col.value('@OrderNumber', 'int') AS 'Order',
Col.value('@OrderDate', 'date') AS 'OrderDate',
Col.value('(Address[@Type=''Shipping'']/Name/text())[1]', 'nvarchar(50)') AS 'Shipping_Name',
Col.value('(Address[@Type=''Shipping'']/Street/text())[1]', 'nvarchar(50)') AS 'Shipping_Street',
Col.value('(Address[@Type=''Shipping'']/City/text())[1]', 'nvarchar(50)') AS 'Shipping_City',
Col.value('(Address[@Type=''Shipping'']/State/text())[1]', 'nvarchar(50)') AS 'Shipping_State',
Col.value('(Address[@Type=''Shipping'']/Zip/text())[1]', 'nvarchar(50)') AS 'Shipping_Zip',
Col.value('(Address[@Type=''Shipping'']/Country/text())[1]', 'nvarchar(50)') AS 'Shipping_Country',
Col.value('(Address[@Type=''Billing'']/Name/text())[1]', 'nvarchar(50)') AS 'Billing_Name',
Col.value('(Address[@Type=''Billing'']/Street/text())[1]', 'nvarchar(50)') AS 'Billing_Street',
Col.value('(Address[@Type=''Billing'']/City/text())[1]', 'nvarchar(50)') AS 'Billing_City',
Col.value('(Address[@Type=''Billing'']/State/text())[1]', 'nvarchar(50)') AS 'Billing_State',
Col.value('(Address[@Type=''Billing'']/Zip/text())[1]', 'nvarchar(50)') AS 'Billing_Zip',
Col.value('(Address[@Type=''Billing'']/Country/text())[1]', 'nvarchar(50)') AS 'Billing_Country',
Col.value('(DeliveryNotes/text())[1]', 'nvarchar(250)') AS 'DeliveryNotes'
FROM @MyXML.nodes('/Orders/Order') AS T(Col)
SELECT
Col.value('(../../../Order/@OrderNumber)[1]', 'int') AS 'Order',
Col.value('(@PartNumber)[1]', 'nvarchar(50)') AS 'PartNumber' ,
Col.value('(ProductName/text())[1]', 'nvarchar(250)') AS 'ProductName' ,
Col.value('(Quantity/text())[1]', 'int') AS 'Quantity',
Col.value('(USPrice/text())[1]', 'float') AS 'USPrice',
Col.value('(Comment/text())[1]', 'nvarchar(250)') AS 'Comment',
Col.value('(ShipDate/text())[1]', 'date') AS 'ShipDate'
FROM @MyXML.nodes('/Orders/Order/Items/Item') AS T(Col)
Here is the Output:
XML File:
HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
