Convert Xml to Table SQL Server

sql

xml

openxml

xquery

byAlex Kataev·Aug 20, 2024

To convert XML data into a table in SQL Server, the powerful nodes() XML method splits the XML into distinct rows, and the value() function extracts the necessary data. Here is a straightforward application:

DECLARE @xmlData XML = '<Items><Item><ID>1</ID><Name>Item1</Name></Item><Item><ID>2</ID><Name>Item2</Name></Item></Items>';

-- Selecting complexity in action!
SELECT
  x.item.value('ID[1]', 'INT') AS ItemID,
  x.item.value('Name[1]', 'VARCHAR(100)') AS ItemName
FROM @xmlData.nodes('/Items/Item') AS x(item);

This script creates rows for each <Item> and plucks out the ID and Name for a seamless XML to SQL transformation.

A closer look at alternatives and best practices

Diving into more complex XML structures, OPENXML, sp_xml_preparedocument and sp_xml_removedocument come to our rescue. Here's a crash course:

DECLARE @xmlData NVARCHAR(MAX)
SET @xmlData = N'<Items><Item ID="1" Name="Item1" /><Item ID="2" Name="Item2" /></Items>'

DECLARE @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlData

-- Reading XML like a pro!
SELECT *
FROM OPENXML(@idoc, '/Items/Item', 2)
WITH 
(
  ItemID INT '@ID',
  ItemName VARCHAR(100) '@Name'
)

-- Clean up is just as important!
EXEC sp_xml_removedocument @idoc

OPENXML acts as a middleman, making your XML data feel more at home with SQL. Always make sure to perform a clean up with sp_xml_removedocument or risk leaving memory-consuming lingering sessions.

Tackling variances in XML

Feeling bold dealing with varying XML elements? Well, proper mapping to columns is your survival kit. Here's your toolset:

For Nullable columns: The handy NULLIF function gracefully handles unsettling data type errors.
For Varying structure: Here, dynamic SQL generation or conditional checks are your best wingmen.

Handling data precision like a boss

Preserve data type precision when translating XML to SQL. XQuery type casting within your value() function will keep your data intact.

Dodging common pitfalls

Take note of namespaces in your XML, they love playing hide-and-seek with your data! Here's how to catch them:

WITH XMLNAMESPACES ('http://example.com/some-namespace' as ns)
-- "ns" stands for "namespace". Cute, huh?
SELECT 
  x.item.value('ns:ID[1]', 'INT') as ItemID
-- Spotted ya!
FROM @xmlData.nodes('/ns:Items/ns:Item') as x(item)