HOW TO CONVERT TABLE DATA TO XML AND VICEVERSA.
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)
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
(
@strTableASnvarchar(128),– Name of the table to convert
@strSchemaASnvarchar(128),– Schema name of the table
@xmlTableSchemaASxml OUTPUT,– Return value of the table schema xml
@xmlTableDataASxml OUTPUT,– Return value of the table data xml
@strFilterASnvarchar(MAX) = ”– optional WHERE string
)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @intReturnValue ASint = 0;
DECLARE @strSQLASnvarchar(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
(
@xmlTableSchemaASxml,– XML string with the table schema created by procConvertTableToXML
@xmlTableDataASxml,– XML string with the table data created by procConvertTableToXML
@strFilterASnvarchar(MAX) = ”– optional WHERE string
)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @intReturnValue ASint = 0;
DECLARE @strSQLASnvarchar(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