HOW TO CONVERT TABLE DATA TO XML AND XML TO TABLE

HOW TO CONVERT TABLE DATA TO XML AND VICEVERSA.

Table to XML and XML to Table

The below code explains how to convert the data in a table to xml form and then convert the xml back into table data.

Creating sample table with data:

CREATE TABLE tmpEmployee(ID INT,NAME VARCHAR(100))
GO

INSERT INTO tmpEmployee
SELECT 1,'Devi'
union
SELECT 2,'Prasad'
GO


SELECT * FROM tmpEmployee
GO

Below code converts data and schema of table tmpEmployee into XML, Then it uses the XML to convert it back into the table:

/*Below code converts Table SCHEMA & Data to XML*/

DECLARE @TableData XML,@TableSchema XML

SELECT @TableSchema = (
select column_name,
data_type,
case(is_nullable)
when 'YES' then 'true'
else 'false'
end as is_nullable,
CHARACTER_MAXIMUM_LENGTH as Charlen
from information_schema.columns [column]
where table_name = 'tmpEmployee'
for xml auto,root('Table')
)

SELECT @TableData = (
SELECT *
FROM tmpEmployee Row
FOR XML AUTO, BINARY BASE64,root('TableData')
)

SELECT @TableSchema,@TableData

/*Below code converts XML to Table*/

if object_id('tempdb..#XMLColumns') is not null
drop table #XMLColumns

SELECT x.value('@column_name', 'sysname') AS column_name
,x.value('@data_type', 'sysname') AS data_type
,x.value('@is_nullable', 'VARCHAR(20)') AS is_nullable
,x.value('@Charlen', 'VARCHAR(20)') AS Charlen
into #XMLColumns
FROM @TableSchema.nodes('/Table/column') TempXML (x)

select * from #XMLColumns

DECLARE @SQL nVARCHAR(MAX) = 'SELECT '

SELECT @SQL = @SQL + '
x.value(''@'+column_name+''', '''+data_type+case when Charlen is null then '' else '('+Charlen+')' end + ''''+') AS ['+column_name+'],'
from #XMLColumns

SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)

SELECT @SQL = @SQL + ' FROM @TableData.nodes(''/TableData/Row'') TempXML (x)'

EXEC sp_executeSQl @SQL,N'@TableData xml',@TableData=@TableData

Output:

tmpEmployee:

ID NAME
----------- ----------------------------------------------------------------------------------------------------
1 Devi
2 Prasad


(2 row(s) affected)

@TableSchema:

<Table>
<column column_name="ID" data_type="int" is_nullable="true" />
<column column_name="NAME" data_type="varchar" is_nullable="true" Charlen="100" />
</Table>

@TableData:

<TableData>
<Row ID="1" NAME="Devi" />
<Row ID="2" NAME="Prasad" />
</TableData>

Table generated from XML:

ID NAME
----------- ----------------------------------------------------------------------------------------------------
1 Devi
2 Prasad


(2 row(s) affected)

One thought on “HOW TO CONVERT TABLE DATA TO XML AND XML TO TABLE

  1. Thanks for this method, works great, also with views instead of tables.

    I’ve made two stored procedures from this to use that with every table/view in the database, maybe useful for other readers of your blog:

    — ===================================================================================================================================
    — Author: Devi Prasad / Christian Coppes
    — Original from: http://sqlserverlearner.com/2013/how-to-convert-table-data-to-xml-and-vice-versa
    — Create date: 15.07.2015
    — Last Change: 15.07.2015
    — Description: Creates XML strings (schema and data) from a specified table
    — ===================================================================================================================================

    CREATE PROCEDURE dbo.procConvertTableToXML
    (
    @strTable AS nvarchar(128), — Name of the table to convert
    @strSchema AS nvarchar(128), — Schema name of the table
    @xmlTableSchema AS xml OUTPUT, — Return value of the table schema xml
    @xmlTableData AS xml OUTPUT, — Return value of the table data xml
    @strFilter AS nvarchar(MAX) = ” — optional WHERE string
    )
    AS
    BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @intReturnValue AS int = 0;
    DECLARE @strSQL AS nvarchar(MAX);

    BEGIN TRY
    SELECT @xmlTableSchema = (SELECT C.COLUMN_NAME, C.DATA_TYPE,
    CASE (C.IS_NULLABLE)
    WHEN ‘YES’ THEN ‘true’
    ELSE ‘false’
    END AS IS_NULLABLE,
    (CASE C.CHARACTER_MAXIMUM_LENGTH
    WHEN -1 THEN ‘MAX’
    ELSE CAST(C.CHARACTER_MAXIMUM_LENGTH AS nvarchar(10)) END) AS CHARLENGTH
    FROM INFORMATION_SCHEMA.COLUMNS AS C
    WHERE C.TABLE_NAME = @strTable
    AND C.TABLE_SCHEMA = @strSchema
    FOR XML AUTO, ROOT(‘Table’)
    );

    — SELECT * is OK here as this is dynamic SQL
    SET @strSQL = ‘SET @xmlTableData = (SELECT TableRow.*
    FROM [‘ + @strSchema + ‘].[‘ + @strTable + ‘] AS TableRow’
    + (CASE WHEN ISNULL(@strFilter,”) = ” THEN ” ELSE ‘ WHERE ‘ + @strFilter END) +
    ‘ FOR XML AUTO, BINARY BASE64, ROOT(”TableData”))’;

    EXEC sys.sp_executesql @strSQL, N’@xmlTableData xml OUTPUT’,@xmlTableData = @xmlTableData OUTPUT;
    END TRY
    BEGIN CATCH
    RETURN -1;
    END CATCH

    LabelExit:
    RETURN @intReturnValue;

    END

    —————————————

    — ===================================================================================================================================
    — Author: Devi Prasad / Christian Coppes
    — Original from: http://sqlserverlearner.com/2013/how-to-convert-table-data-to-xml-and-vice-versa
    — Create date: 15.07.2015
    — Last Change: 15.07.2015
    — Description: Creates XML strings (schema and data) from a specified table
    — ===================================================================================================================================

    CREATE PROCEDURE dbo.procConvertXMLToTable
    (
    @xmlTableSchema AS xml, — XML string with the table schema created by procConvertTableToXML
    @xmlTableData AS xml, — XML string with the table data created by procConvertTableToXML
    @strFilter AS nvarchar(MAX) = ” — optional WHERE string
    )
    AS
    BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @intReturnValue AS int = 0;
    DECLARE @strSQL AS nvarchar(MAX) = ”;

    BEGIN TRY

    IF OBJECT_ID(‘tempdb..#tblXMLColumns’) IS NOT NULL
    DROP TABLE #tblXMLColumns;

    SELECT x.value(‘@COLUMN_NAME’, ‘sysname’) AS COLUMN_NAME,
    x.value(‘@DATA_TYPE’, ‘sysname’) AS DATA_TYPE,
    x.value(‘@IS_NULLABLE’, ‘VARCHAR(20)’) AS IS_NULLABLE,
    x.value(‘@CHARLENGTH’, ‘VARCHAR(20)’) AS CHARLENGTH
    INTO #tblXMLColumns
    FROM @xmlTableSchema.nodes(‘/Table/C’) TempXML (x);

    SET @strSQL = ‘ WITH Q AS (SELECT ‘;
    SELECT @strSQL = @strSQL + ‘x.value(”@’ + COLUMN_NAME + ”’, ”’ + DATA_TYPE +
    (CASE when CHARLENGTH is null then ” else ‘(‘ + CHARLENGTH + ‘)’ END) +
    ””+’) AS [‘ + COLUMN_NAME + ‘],’
    FROM #tblXMLColumns;

    SET @strSQL = LEFT(@strSQL, LEN(@strSQL) – 1);

    SELECT @strSQL = @strSQL + ‘ FROM @xmlTableData.nodes(”/TableData/TableRow”) TempXML (x)) SELECT * FROM Q ‘
    + (CASE WHEN ISNULL(@strFilter,”) = ” THEN ” ELSE ‘ WHERE ‘ + @strFilter END);

    EXEC sys.sp_executesql @strSQL,N’@xmlTableData xml’,@xmlTableData = @xmlTableData;

    END TRY
    BEGIN CATCH
    RETURN -1;
    END CATCH

    LabelExit:
    RETURN @intReturnValue;

    END

    ————————————————————–

    They can be used like this:

    DECLARE @return_value int,
    @xmlTableSchema xml,
    @xmlTableData xml

    EXEC @return_value = dbo.procConvertTableToXML
    @strTable = N’NameOfTheTable’,
    @strSchema = N’SchemaName’,
    @xmlTableSchema = @xmlTableSchema OUTPUT,
    @xmlTableData = @xmlTableData OUTPUT,
    @strFilter = ‘Optional Filter like “ID < 100"'

    SELECT @xmlTableSchema as N'@xmlTableSchema',
    @xmlTableData as N'@xmlTableData'

    SELECT 'Return Value' = @return_value

    EXEC @return_value = dbo.procConvertXMLToTable
    @xmlTableSchema = @xmlTableSchema,
    @xmlTableData = @xmlTableData,
    @strFilter = 'Optional Filter like "ID < 100"'

    GO

    ——————————————————–

    Possible issue: If the amount of data is too big it returns only the schema.

    Thanks for the idea,

    Christian

Leave a Reply

Your email address will not be published. Required fields are marked *