Store and retrieve data into XML from table in SQL Server

The aim of this post is to store data in a table in the form of XML along with schema, and to retrieve the same from XML in a tabular structure.

Let us take an example of a table say Employee:

CREATE TABLE Employee

(

       ID INT,

       FName VARCHAR(200),

       LNAME VARCHAR(200),

       Salary FLOAT

);

INSERT INTO Employee(ID, FName, LName, Salary)

VALUES(1,'A','B',10.1),

(2,'C','D',12.123),

(3,'E','F',123.13)

DECLARE @xml XML

SELECT @xml = (

       SELECT

              ID,

              FName,

              LName,

              Salary

       FROM Employee

       FOR XML AUTO, ELEMENTS, XMLSCHEMA('AnySchema'), ROOT('Root');

)

SELECT @xml

This is how Table can be stored in XML format along with schema. If you see the output XML, it will have two portions, one with schema and another with actual data.

Below, is how we can get back the data from XML into tabular structure.

DECLARE @XSD XML, @sql VARCHAR(MAX), @TableName VARCHAR(100)

SET @XSD = (SELECT

              x.query('.')

       FROM @XML.nodes('/Root/*[1]') a (x))

SELECT

       @XML = (SELECT

                     CAST(STUFF(

                     CAST(@XML.query('*[1]/*') AS VARCHAR(MAX))

                     , 1

                     , CHARINDEX(

                     '</xsd:schema>'

                     , CAST(@XML.query('*[1]/*') AS VARCHAR(MAX))

                     ) + 12

                     , ''

                     ) AS XML)

              FOR XML RAW ('Root'))

SELECT

       @XML = CAST(REPLACE(CAST(@XML AS VARCHAR(MAX)), ' xmlns="AnySchema"', '') AS XML)

SELECT

       @TableName = a.x.value('@name', 'varchar(100)')

FROM @XSD.nodes('/*/*') a (x)

SELECT

       @XSD = (SELECT

                     a.x.query('.')

              FROM @XSD.nodes('/*/*[2]/*/*') a (x))

SELECT

       @XSD = CAST(REPLACE(CAST(@XSD AS VARCHAR(MAX)), 'p1:', '') AS XML)

IF @XSD IS NULL

BEGIN

SELECT

       'XML Schema was not specified'

RETURN

END

IF EXISTS (SELECT

              1

       FROM Tempdb..sysobjects

       WHERE id = OBJECT_ID('Tempdb..#TableDefinition'))

DROP TABLE #TableDefinition

CREATE TABLE #TableDefinition

(

       ID INT IDENTITY(1,1),

       ColumnName VARCHAR(255),

       ColumnDataType1 VARCHAR(255),

       ColumnDataType2 VARCHAR(255),

       ColumnDataType2Length VARCHAR(255),

       hasLength INT

)

INSERT INTO #TableDefinition

              SELECT

                     a.x.value('@name', 'varchar(100)') ColumnName

                     ,REPLACE(CAST(a.x.value('@type', 'sysname') AS VARCHAR(100)), 'sqltypes:', '') ColumnDataType1

                     ,REPLACE(CAST(a.x.value('*[1]/*[1]/@base', 'sysname') AS VARCHAR(100)), 'sqltypes:', '') ColumnDataType2

                     ,REPLACE(CAST(a.x.value('*[1]/*[1]/*[1]/@value', 'sysname') AS VARCHAR(100)), 'sqltypes:', '') ColumnDataType2Length

                     ,0 AS hasLength

              FROM @XSD.nodes('/*/*') a (x)

UPDATE #TableDefinition

SET    hasLength = 1

       ,ColumnDataType2Length =

       CASE

              WHEN ColumnDataType2 IN ('numeric', 'decimal') THEN RTRIM(ColumnDataType2Length) + ',10'

              ELSE ColumnDataType2Length

       END

WHERE ColumnDataType2Length IS NOT NULL

DECLARE @TableDefinition VARCHAR(MAX)

SET @TableDefinition = (SELECT

              CAST(CASE

                     WHEN hasLength = 1 THEN ColumnName + ' ' + ColumnDataType2 + '(' + ColumnDataType2Length + '),'

                     ELSE ColumnName + ' ' + ColumnDataType1 + ','

              END AS VARCHAR(MAX))

       FROM #TableDefinition a

       FOR XML PATH (''))

SELECT

       @TableDefinition = SUBSTRING(@TableDefinition, 0, LEN(@TableDefinition))

IF EXISTS (SELECT

              1

       FROM Tempdb..sysobjects

       WHERE id = OBJECT_ID('Tempdb..#XML'))

DROP TABLE #XML

SELECT

       @XML AS XMLDATA INTO #XML

SELECT

       @sql =

       ' DECLARE @handle INT, @PrepareXMLStatus INT, @XML2 XML

  SET @XML2 = (SELECT XMLDATA FROM #XML);

  EXEC @PrepareXMLStatus = sp_xml_preparedocument @handle OUTPUT, @XML2;

  SELECT *

  FROM OPENXML(@handle, ''/Root/' + @TableName + ''', 2)

       WITH (' + @TableDefinition + ')

  EXEC sp_xml_removedocument @handle;'

EXEC (@sql)