Convert XML to table and table to XML

We have a lot of articles about how to do but most of the time it is too much information.

This article tries to answer 2 questions!

How can we create XML column from a SQL query?
How can we create a table from XML column?

This example use NorthWind database:

https://northwinddatabase.codeplex.com/releases/view/71634

USE [NORTHWND]

GO

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

GO

BEGIN TRY DROP TABLE [dbo].[CustomersXML]; END TRY BEGIN CATCH END CATCH;

CREATE TABLE [dbo].[CustomersXML](

       [CustomersXMLID] [int] IDENTITY(1,1) NOT NULL,

       [XMLCode] [xml] NULL,

 CONSTRAINT [PK_CustomersXML] PRIMARY KEY CLUSTERED

(

       [CustomersXMLID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

GO

SQL Script to convert between Query and XML

---------------------------------------------------------

-- Example 1 Query -- >> XML

-- Attention : this example use "for xml auto, ELEMENTS, root('TableData')"

/*

<TableData>

  <Customer>

    <RowNumber>1</RowNumber>

    <CustomerID>ALFKI</CustomerID>

    <ContactName>Maria Anders</ContactName>

    <CompanyName>Alfreds Futterkiste</CompanyName>

    <City>Berlin</City>

  </Customer>

</TableData>

*/

 

TRUNCATE TABLE [CustomersXML];

DECLARE @RowNumber int;

 

BEGIN TRY DROP TABLE #tmp; END TRY BEGIN CATCH END CATCH;

SELECT ROW_NUMBER() over (ORDER BY CustomerID) as RowNumber, CustomerID,ContactName,CompanyName,City

INTO #tmp

FROM Customers

-- FOR XML AUTO--, ELEMENTS--, XMLSCHEMA;

DECLARE @xml xml;

WHILE (exists(select * from #tmp))

BEGIN

 

       SELECT TOP 1 @RowNumber=RowNumber FROM #tmp;

       SELECT @xml = (

                     SELECT RowNumber, CustomerID, ContactName, CompanyName, City

                     FROM #tmp as Customer  WHERE @RowNumber=RowNumber FOR XML AUTO, elements, root('TableData')-- AUTO,

                     )

       INSERT INTO [CustomersXML](XMLCode) VALUES (@xml);

       DELETE FROM #tmp WHERE @RowNumber=RowNumber;

END

 

 

SELECT top 1 * FROM [dbo].[CustomersXML];

 

Convert XML to table

-- Method 1

SELECT 

       [XMLCode].value('(/TableData/Customer/RowNumber)[1]', 'int') as RowNumber, 

       [XMLCode].value('(/TableData/Customer/CustomerID)[1]', 'nvarchar(max)') as CustomerID, 

       [XMLCode].value('(/TableData/Customer/ContactName)[1]', 'nvarchar(max)') as ContactName,

       [XMLCode].value('(/TableData/Customer/CompanyName)[1]', 'nvarchar(max)') as CompanyName,

       [XMLCode].value('(/TableData/Customer/City)[1]', 'nvarchar(max)')  as City

FROM   [CustomersXML]

WHERE [XMLCode].exist('/TableData/Customer') = 1

-- Method 2

SELECT 

        x.m.value('(RowNumber)[1]', 'int') as RowNumber, 

        x.m.value('(CustomerID)[1]', 'nvarchar(max)') as CustomerID, 

        x.m.value('(ContactName)[1]', 'nvarchar(max)') as ContactName,

        x.m.value('(CompanyName)[1]', 'nvarchar(max)') as CompanyName,

        x.m.value('(City)[1]', 'nvarchar(max)')  as City

FROM   [CustomersXML] xt

cross apply xt.XMLCode.nodes('TableData/Customer') x(m)

-- Example 2 Query -- >> XML

-- Attention : this example use "for xml auto, root('TableData')"

/*

<TableData>

  <Customer RowNumber="1" CustomerID="ALFKI" ContactName="Maria Anders" CompanyName="Alfreds Futterkiste" City="Berlin" />

</TableData>

*/

 

TRUNCATE TABLE [CustomersXML];

DECLARE @RowNumber int;

 

BEGIN TRY DROP TABLE #tmp; END TRY BEGIN CATCH END CATCH;

SELECT ROW_NUMBER() over (ORDER BY CustomerID) as RowNumber, CustomerID,ContactName,CompanyName,City

INTO #tmp

FROM Customers

-- FOR XML AUTO--, ELEMENTS--, XMLSCHEMA;

DECLARE @xml xml;

WHILE (exists(select * from #tmp))

BEGIN

 

       SELECT TOP 1 @RowNumber=RowNumber FROM #tmp;

       SELECT @xml = (

                     SELECT RowNumber, CustomerID, ContactName, CompanyName, City

                     FROM #tmp as Customer  WHERE @RowNumber=RowNumber FOR XML AUTO, root('TableData')-- AUTO,

                     )

       INSERT INTO [CustomersXML](XMLCode) VALUES (@xml);

       DELETE FROM #tmp WHERE @RowNumber=RowNumber;

END

 

 

SELECT top 1 * FROM [dbo].[CustomersXML];

Convert XML to table

-- Sample 2 Query << -- XML

 

SELECT top 1 * FROM [dbo].[CustomersXML];

 

-- Method 1

SELECT 

       [XMLCode].value('(/TableData/Customer/@RowNumber)[1]', 'int') as RowNumber, 

       [XMLCode].value('(/TableData/Customer/@CustomerID)[1]', 'nvarchar(max)') as CustomerID, 

       [XMLCode].value('(/TableData/Customer/@ContactName)[1]', 'nvarchar(max)') as ContactName,

       [XMLCode].value('(/TableData/Customer/@CompanyName)[1]', 'nvarchar(max)') as CompanyName,

       [XMLCode].value('(/TableData/Customer/@City)[1]', 'nvarchar(max)')  as City

FROM   [CustomersXML]

-- WHERE [XMLCode].exist('/TableData/Customer') = 1  -- Just to make sure

 

-- Method 2 

SELECT 

       x.m.value('@RowNumber[1]', 'int') as RowNumber, 

       x.m.value('@CustomerID[1]', 'nvarchar(max)') as CustomerID, 

       x.m.value('@ContactName[1]', 'nvarchar(max)') as ContactName,

       x.m.value('@CompanyName[1]', 'nvarchar(max)') as CompanyName,

       x.m.value('@City[1]', 'nvarchar(max)')  as City

FROM   [CustomersXML] xt

cross apply xt.XMLCode.nodes('TableData/Customer') x(m)