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

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)



Reference : Devi Prasad (sqlserverlearner.com)

Do you like my blog?

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



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.




Your Ad Here