sqltoxml

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

Ashish Patel

Create Multi-Layer XML file from SQL Database Table

In this article, I talked about how to create multilayer xml from SQL Server Database table.
Following is the XML which we want to create:
[ad#post]

<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>CA</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>PA</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>

We have two table called Orders and Order_Products

XML_To_DB

SQL Script to generate above XML:

SELECT [Order] as '@OrderNumber' , OrderDate as '@OrderDate',
(
     SELECT 'Shipping' as '@Type' 
     , Shipping_Name as 'Name'
     ,Shipping_Street as 'Street'
     ,Shipping_City as 'City'
     ,Shipping_State as 'State'
     ,Shipping_State as 'Zip'
     ,Shipping_Country as 'Country'
     FOR XML PATH('Address'),Type
  ),
  (
     SELECT 'Billing' as '@Type' 
     , Billing_Name as 'Name'
     ,Billing_Street as 'Street'
     ,Billing_City as 'City'
     ,Billing_State as 'State'
     ,Billing_State as 'Zip'
     ,Billing_Country as 'Country'
     FOR XML PATH('Address'),Type
  ),DeliveryNotes,
  (
    SELECT PartNumber as '@PartNumber', ProductName, Quantity,convert(numeric(18,2), USPrice)as USPrice,Comment,ShipDate 			
		From [temp].[dbo].Order_Products P where 	P.[Order] =Ord.[Order]
		FOR XML PATH('Item'),ROOT('Items'),Type		
   )   
FROM [Orders] Ord
FOR XML PATH('Order'),ROOT('Orders')

Thanks