Ashish Patel

Import XML file to Database Table

In this article, I talked about how to parse XML or import XML to SQL Server Database Table.
XML File:

<br />
<?xml version="1.0"?><br />
<Orders><br />
<Order OrderNumber="99503" OrderDate="2013-10-20"><br />
  <Address Type="Shipping"><br />
    <Name>Ellen Adams</Name><br />
    <Street>123 Maple Street</Street><br />
    <City>Mill Valley</City><br />
    <State>CA</State><br />
    <Zip>10999</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <Address Type="Billing"><br />
    <Name>Tai Yee</Name><br />
    <Street>8 Oak Avenue</Street><br />
    <City>Old Town</City><br />
    <State>PA</State><br />
    <Zip>95819</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes><br />
  <Items><br />
    <Item PartNumber="872-AA"><br />
      <ProductName>Lawnmower</ProductName><br />
      <Quantity>1</Quantity><br />
      <USPrice>148.95</USPrice><br />
      <Comment>Confirm this is electric</Comment><br />
    </Item><br />
    <Item PartNumber="926-AA"><br />
      <ProductName>Baby Monitor</ProductName><br />
      <Quantity>2</Quantity><br />
      <USPrice>39.98</USPrice><br />
      <ShipDate>2013-05-21</ShipDate><br />
    </Item><br />
  </Items><br />
</Order><br />
</Orders><br />

To Query XML File we need to store in to xml variable @MyXML:

<br />
DECLARE @MyXML XML<br />
SET @MyXML = '<?xml version="1.0"?><br />
<Orders><br />
<Order OrderNumber="99503" OrderDate="2013-10-20"><br />
  <Address Type="Shipping"><br />
    <Name>Ellen Adams</Name><br />
    <Street>123 Maple Street</Street><br />
    <City>Mill Valley</City><br />
    <State>CA</State><br />
    <Zip>10999</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <Address Type="Billing"><br />
    <Name>Tai Yee</Name><br />
    <Street>8 Oak Avenue</Street><br />
    <City>Old Town</City><br />
    <State>PA</State><br />
    <Zip>95819</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes><br />
  <Items><br />
    <Item PartNumber="872-AA"><br />
      <ProductName>Lawnmower</ProductName><br />
      <Quantity>1</Quantity><br />
      <USPrice>148.95</USPrice><br />
      <Comment>Confirm this is electric</Comment><br />
    </Item><br />
    <Item PartNumber="926-AA"><br />
      <ProductName>Baby Monitor</ProductName><br />
      <Quantity>2</Quantity><br />
      <USPrice>39.98</USPrice><br />
      <ShipDate>2013-05-21</ShipDate><br />
    </Item><br />
  </Items><br />
</Order><br />
</Orders>'<br />

Now we use SQL query to get Order header from @MyXML:

<br />
SELECT Col.value('@OrderNumber', 'int') AS 'Order',<br />
Col.value('@OrderDate', 'date') AS 'OrderDate',<br />
Col.value('(Address[@Type=''Shipping'']/Name/text())[1]', 'nvarchar(50)') AS 'Shipping_Name',<br />
Col.value('(Address[@Type=''Shipping'']/Street/text())[1]', 'nvarchar(50)') AS 'Shipping_Street',<br />
Col.value('(Address[@Type=''Shipping'']/City/text())[1]', 'nvarchar(50)') AS 'Shipping_City',<br />
Col.value('(Address[@Type=''Shipping'']/State/text())[1]', 'nvarchar(50)') AS 'Shipping_State',<br />
Col.value('(Address[@Type=''Shipping'']/Zip/text())[1]', 'nvarchar(50)') AS 'Shipping_Zip',<br />
Col.value('(Address[@Type=''Shipping'']/Country/text())[1]', 'nvarchar(50)') AS 'Shipping_Country',<br />
Col.value('(Address[@Type=''Billing'']/Name/text())[1]', 'nvarchar(50)') AS 'Billing_Name',<br />
Col.value('(Address[@Type=''Billing'']/Street/text())[1]', 'nvarchar(50)') AS 'Billing_Street',<br />
Col.value('(Address[@Type=''Billing'']/City/text())[1]', 'nvarchar(50)') AS 'Billing_City',<br />
Col.value('(Address[@Type=''Billing'']/State/text())[1]', 'nvarchar(50)') AS 'Billing_State',<br />
Col.value('(Address[@Type=''Billing'']/Zip/text())[1]', 'nvarchar(50)') AS 'Billing_Zip',<br />
Col.value('(Address[@Type=''Billing'']/Country/text())[1]', 'nvarchar(50)') AS 'Billing_Country',<br />
Col.value('(DeliveryNotes/text())[1]', 'nvarchar(250)') AS 'DeliveryNotes'<br />
FROM @MyXML.nodes('/Orders/Order') AS T(Col)<br />

Now we use SQL query to get Order Items from @MyXML:

<br />
SELECT<br />
Col.value('(../../../Order/@OrderNumber)[1]', 'int') AS 'Order',<br />
Col.value('(@PartNumber)[1]', 'nvarchar(50)') AS 'PartNumber' ,<br />
Col.value('(ProductName/text())[1]', 'nvarchar(250)') AS 'ProductName' ,<br />
Col.value('(Quantity/text())[1]', 'int') AS 'Quantity',<br />
Col.value('(USPrice/text())[1]', 'float') AS 'USPrice',<br />
Col.value('(Comment/text())[1]', 'nvarchar(250)') AS 'Comment',<br />
Col.value('(ShipDate/text())[1]', 'date') AS 'ShipDate'<br />
FROM @MyXML.nodes('/Orders/Order/Items/Item') AS T(Col)<br />

Full Code:

<br />
DECLARE @MyXML XML<br />
SET @MyXML = '<?xml version="1.0"?><br />
<Orders><br />
<Order OrderNumber="99503" OrderDate="2013-10-20"><br />
  <Address Type="Shipping"><br />
    <Name>Ellen Adams</Name><br />
    <Street>123 Maple Street</Street><br />
    <City>Mill Valley</City><br />
    <State>CA</State><br />
    <Zip>10999</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <Address Type="Billing"><br />
    <Name>Tai Yee</Name><br />
    <Street>8 Oak Avenue</Street><br />
    <City>Old Town</City><br />
    <State>PA</State><br />
    <Zip>95819</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes><br />
  <Items><br />
    <Item PartNumber="872-AA"><br />
      <ProductName>Lawnmower</ProductName><br />
      <Quantity>1</Quantity><br />
      <USPrice>148.95</USPrice><br />
      <Comment>Confirm this is electric</Comment><br />
    </Item><br />
    <Item PartNumber="926-AA"><br />
      <ProductName>Baby Monitor</ProductName><br />
      <Quantity>2</Quantity><br />
      <USPrice>39.98</USPrice><br />
      <ShipDate>2013-05-21</ShipDate><br />
    </Item><br />
  </Items><br />
</Order><br />
</Orders>'<br />
   SELECT<br />
    Col.value('@OrderNumber', 'int') AS 'Order',<br />
    Col.value('@OrderDate', 'date') AS 'OrderDate',<br />
    Col.value('(Address[@Type=''Shipping'']/Name/text())[1]', 'nvarchar(50)') AS 'Shipping_Name',<br />
    Col.value('(Address[@Type=''Shipping'']/Street/text())[1]', 'nvarchar(50)') AS 'Shipping_Street',<br />
    Col.value('(Address[@Type=''Shipping'']/City/text())[1]', 'nvarchar(50)') AS 'Shipping_City',<br />
    Col.value('(Address[@Type=''Shipping'']/State/text())[1]', 'nvarchar(50)') AS 'Shipping_State',<br />
    Col.value('(Address[@Type=''Shipping'']/Zip/text())[1]', 'nvarchar(50)') AS 'Shipping_Zip',<br />
    Col.value('(Address[@Type=''Shipping'']/Country/text())[1]', 'nvarchar(50)') AS 'Shipping_Country',<br />
    Col.value('(Address[@Type=''Billing'']/Name/text())[1]', 'nvarchar(50)') AS 'Billing_Name',<br />
    Col.value('(Address[@Type=''Billing'']/Street/text())[1]', 'nvarchar(50)') AS 'Billing_Street',<br />
    Col.value('(Address[@Type=''Billing'']/City/text())[1]', 'nvarchar(50)') AS 'Billing_City',<br />
    Col.value('(Address[@Type=''Billing'']/State/text())[1]', 'nvarchar(50)') AS 'Billing_State',<br />
    Col.value('(Address[@Type=''Billing'']/Zip/text())[1]', 'nvarchar(50)') AS 'Billing_Zip',<br />
    Col.value('(Address[@Type=''Billing'']/Country/text())[1]', 'nvarchar(50)') AS 'Billing_Country',<br />
    Col.value('(DeliveryNotes/text())[1]', 'nvarchar(250)') AS 'DeliveryNotes'<br />
  FROM  @MyXML.nodes('/Orders/Order') AS T(Col)</p>
<p>   SELECT<br />
    Col.value('(../../../Order/@OrderNumber)[1]', 'int') AS 'Order',<br />
    Col.value('(@PartNumber)[1]', 'nvarchar(50)') AS 'PartNumber'  ,<br />
    Col.value('(ProductName/text())[1]', 'nvarchar(250)') AS 'ProductName' ,<br />
    Col.value('(Quantity/text())[1]', 'int') AS 'Quantity',<br />
    Col.value('(USPrice/text())[1]', 'float') AS 'USPrice',<br />
    Col.value('(Comment/text())[1]', 'nvarchar(250)') AS 'Comment',<br />
    Col.value('(ShipDate/text())[1]', 'date') AS 'ShipDate'<br />
  FROM  @MyXML.nodes('/Orders/Order/Items/Item') AS T(Col)<br />

XML_To_DB

Thanks

Posted in ASP.Net, SQL ServerTagged , , , Leave a comment

Leave a Reply