Process XML in SQL Server using OPENXML

Aim: In my last blog post, I recorded a sample query for processing JSON in SQL Server so that I can refer back to it & tailor it to my needs when I need it in future. In this post, I’m going to repeat that exercise for XML.

I’ve downloaded a sample of XML from https://docs.microsoft.com/en-us/bingads/shopping-content/json-xml-examples. This short sample contains data nested at different levels, elements & attributes, & is therefore a useful starting point for my query as these are likely to occur in my real world data too. It can be helpful to format a sample of the XML so that it is properly indented (as below) to easily see what level each data value is at. This can be done via a number of online formatting tools or plugins for text editors like Notepad++.

First, declare a variable to hold the XML. Alternatively, the XML can be stored in a column in a table. As SQL Server has a built-in XML data type, it will help to validate our string.

DECLARE @xml XML = '<?xml version="1.0" encoding="utf-8"?>
<product>
  <additional_image_link>http://www.tailspintoys.com/images?id=123def</additional_image_link>
  <additional_image_link>http://www.tailspintoys.com/images?id=567def</additional_image_link>
  <adult>False</adult>
  <adwords_redirect>http://contoso.com/hury</adwords_redirect>
  <age_group>kids</age_group>
  <availability>in stock</availability>
  <brand>Tailspin</brand>
  <channel>online</channel>
  <color>Blue</color>
  <condition>new</condition>
  <content_language>en</content_language>
  <description>Charming sundress perfect for lunch out on the town.</description>
  <expiration_date>2016-07-14T07:27:06-08:00</expiration_date>
  <gender>female</gender>
  <id>online:en:US:sku5678</id>
  <identifier_exists>True</identifier_exists>
  <image_link>http://www.tailspintoys.com/images?id=123abc</image_link>
  <item_group_id>abc123def456</item_group_id>
  <link>http://www.tailspintoys.com/girls/apparel?id=9d0s-a934</link>
  <material>cotton</material>
  <offer_id>sku5678</offer_id>
  <online_only>False</online_only>
  <price currency="USD">38.0</price>
  <product_type>Apparel & Accessories > Clothing > Dresses</product_type>
  <sale_price currency="USD">25.0</sale_price>
  <sale_price_effective_date>2016-06-14T08:00:00-08:00/2016-06-21T17:00:00-08:00</sale_price_effective_date>
  <shipping>
    <country>US</country>
    <price currency="USD">3.0</price>
    <region>CA</region>
    <service>Ground</service>
  </shipping>
  <shipping_weight unit="oz">1.3</shipping_weight>
  <shipping_label>promotion</shipping_label>
  <size>2T</size>
  <size>3T</size>
  <size_system>US</size_system>
  <size_type>regular</size_type>
  <target_country>US</target_country>
  <tax>
    <country>US</country>
    <rate>9.9</rate>
    <region>CA</region>
    <ship>True</ship>
  </tax>
  <title>Cute Toddler Sundress</title>
</product>';

The following query will process the data & can be amended for other similar datasets.

-- Prepare the XML document. See https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-xml-preparedocument-transact-sql for details
DECLARE @hdoc INT;

EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml;

-- Because we are using SQL Server's built-in XML datatype, our variable does not need to be validated (unlike JSON)
SELECT [Details].[Brand]
	  ,[Details].[Title]
	  ,[Details].[Description]
	  ,[Details].[Currency]
	  ,[Details].[Price]
	  ,[Details].[SalePrice]
	  ,[Details].[ShippingCurrency]
	  ,[Details].[ShippingCost]
	  ,[Details].[ShippingCountry]
FROM OPENXML(@hdoc, '/product')
WITH (
	[Brand] NVARCHAR(32) 'brand' -- top level
	,[Description] NVARCHAR(64) 'description' -- top level
	,[Price] DECIMAL(9, 6) 'price' -- top level
	,[Currency] NCHAR(3) './price/@currency' -- attribute at top level
	,[SalePrice] DECIMAL(9, 6) 'sale_price' -- top level
	,[ShippingCurrency] NCHAR(3) './shipping/price/@currency' -- attribute one level down
	,[ShippingCost] DECIMAL(9, 6) './shipping/price' -- one level down
	,[ShippingCountry] NVARCHAR(32) './shipping/country' -- one level down
	,[Title] NVARCHAR(32) 'title' -- top level
) AS [Details];

-- Remove the prepared XML document - see https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-xml-removedocument-transact-sql for details
EXEC sp_xml_removedocument @hdoc;

The output is the same as the JSON query from the last blog post: