HOW TO CONVERT TABLE DATA TO XML AND XML TO TABLE

Posted on : 28-09-2013 | By : Devi Prasad | In : SQLServerPedia Syndication, T-SQL Code Examples

Share:

1


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)
(Visited 1,885 times, 1 visits today)



References : Devi Prasad (sqlserverlearner.com)

Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Comments (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

Write a comment