Dynamic XML to Table

Question

I am passing table name and XML string to Sql Server SP. I want to insert records to corresponding table. how to read the column name dynamically?

Example : 1
===========
Declare @TableName = 'Table1'
Declare @xml1 xml
set @xml1 = '<Root>
<Nodes>
<Id>01</Id>
<Name>Mike</Name>
</Nodes>
</Root>'

insert into @TableName (
--column names from @xml
) values
(
--select ID from @xml,
--select Name from @xml
)

Example : 2
===========

Declare @TableName = 'Table2'
Declare @xml2 xml
set @xml2 = '<Root>
<Nodes>
<EmpId>05</EmpId>
<EmpName>Andrew</EmpName>
</Nodes>
</Root>'

Kalai · Answer

Thanks for your Reply. Below T-SQL is working. declare @xmlstring xml set @xmlstring = ' 1 Translate() ' SELECT Tbl.Col.value('Id[1]' 'int') as ErrorLogID Tbl.Col.value('Name[1]' 'varchar(100)') as MethodName FROM @xmlstring.nodes('/Root/Node') as Tbl(Col) But value of @xmlstring can be vary. @xmlstring value is passed from web page. I want to read the column name dynamically. Sometimes it can be vary like set @xmlstring = ' 1 Translate() '

Manish Kumar Choudhary · Answer

Hi Kalai  Read these links it helps u http://programcall.com/11/dotnet/how-to-read-data-from-xml-string--and-insert-to-table-sql-server.aspx http://support.microsoft.com/KB/316005