create a procedure to read XML and get data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

create a procedure to read XML and get data

Author

Topic

nitin05.sharma
Starting Member

20 Posts

Posted - 2012-04-26 : 03:37:44

i have convert datatable to XML
and find a string of XML type
<NewDataSet>
<Table1>
<TableName>T_NCO_LNK_AST_AC_MISSN_VARNT</TableName>
<FieldName>EFF_DT</FieldName>
<FieldValue>123</FieldValue>
</Table1>
</NewDataSet>

i want to create a procedure read this XML string and insert into temp table
thanks in advance
please help me
its urgent

nitin05.sharma
Starting Member

20 Posts

Posted - 2012-04-26 : 04:45:54

<NewDataSet>
<Table1>
<TableName>T_NCO_LNK_AST_AC_MISSN_VARNT</TableName>
<FieldName>EFF_DT</FieldName>
<FieldValue>123</FieldValue>
</Table1>
<Table1>
<TableName>T_OBJ</TableName>
<FieldName>OBJ_RID</FieldName>
<FieldValue>1</FieldValue>
</Table1>
</NewDataSet>

XML string may this

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-04-27 : 01:54:03

declare @xml as xml

set @xml='<NewDataSet>
<Table1>
<TableName>T_NCO_LNK_AST_AC_MISSN_VARNT</TableName>
<FieldName>EFF_DT</FieldName>
<FieldValue>123</FieldValue>
</Table1>
<Table1>
<TableName>T_OBJ</TableName>
<FieldName>OBJ_RID</FieldName>
<FieldValue>1</FieldValue>
</Table1>
</NewDataSet>'

select
q.t.value('TableName[1]','varchar(50)') as tableName,
q.t.value('FieldName[1]','varchar(50)') as FieldName,
q.t.value('FieldValue[1]','varchar(50)') as FieldValue

from @xml.nodes('/NewDataSet/Table1') as q(t)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources