xml to datatable - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

xml to datatable

Author

Topic

anujpratap84
Starting Member

45 Posts

Posted - 2012-04-26 : 04:27:02

Hi All,

I have a xml stream and i want to convert it into a tamporary data table.
<?xml version="1.0" encoding="utf-8" ?>
<DataSet>
<NewDataSet>
<TableName>T_NCO_LNK_AST_AC_MISSN_VARNT</TableName>
<FieldName>EFF_DT</FieldName>
<FieldValue>1233</FieldValue>
</NewDataSet>
<NewDataSet>
<TableName>T_NCO_LNK_AST_AC_MISSN_VARNT1</TableName>
<FieldName>EFF_DT1</FieldName>
<FieldValue>1232</FieldValue>
</NewDataSet>
<NewDataSet>
<TableName>T_NCO_LNK_AST_AC_MISSN_VARNT2</TableName>
<FieldName>EFF_DT2</FieldName>
<FieldValue>1231</FieldValue>
</NewDataSet>
</DataSet>

Below is the table format i want.

TableName FieldName FieldValue

Please help

Anuj Pratap Singh

anujpratap84
Starting Member

45 Posts

Posted - 2012-04-26 : 06:08:41

I have found the solution.

DECLARE
@xmlDoc XML
DECLARE
@handle INT
SET
@xmlDoc = N' <NewDataSet>
<Table1>
<TableName>T_NCO_LNK_AST_AC_MISSN_VARNT</TableName>
<FieldName>EFF_DT</FieldName>
<FieldValue>123</FieldValue>
</Table1>

<Table1>
<TableName>T_NCO_LNK_AST_AC_MISSN_VARNT</TableName>
<FieldName>EFF_DT</FieldName>
<FieldValue>123</FieldValue>
</Table1>
</NewDataSet>'
EXEC
sp_xml_preparedocument @handle OUTPUT, @xmlDoc
SELECT
* FROM OPENXML (@handle, '/NewDataSet/Table1', 2) WITH (TableName VARCHAR(20), FieldName NVARCHAR(20), FieldValue NVARCHAR(20) )
EXEC
Sp_xml_removedocument @handle

quote:
Originally posted by anujpratap84

Hi All,

I have a xml stream and i want to convert it into a tamporary data table.
<?xml version="1.0" encoding="utf-8" ?>
<DataSet>
<NewDataSet>
<TableName>T_NCO_LNK_AST_AC_MISSN_VARNT</TableName>
<FieldName>EFF_DT</FieldName>
<FieldValue>1233</FieldValue>
</NewDataSet>
<NewDataSet>
<TableName>T_NCO_LNK_AST_AC_MISSN_VARNT1</TableName>
<FieldName>EFF_DT1</FieldName>
<FieldValue>1232</FieldValue>
</NewDataSet>
<NewDataSet>
<TableName>T_NCO_LNK_AST_AC_MISSN_VARNT2</TableName>
<FieldName>EFF_DT2</FieldName>
<FieldValue>1231</FieldValue>
</NewDataSet>
</DataSet>

Below is the table format i want.

TableName FieldName FieldValue

Please help

Anuj Pratap Singh

Anuj Pratap Singh

anujpratap84
Starting Member

45 Posts

Posted - 2012-04-26 : 06:09:17

quote:
Originally posted by anujpratap84

I have found the solution.

DECLARE
@xmlDoc XML
DECLARE
@handle INT
SET
@xmlDoc = N' <NewDataSet>
<Table1>
<TableName>T_NCO_LNK_AST_AC_MISSN_VARNT</TableName>
<FieldName>EFF_DT</FieldName>
<FieldValue>123</FieldValue>
</Table1>

<Table1>
<TableName>T_NCO_LNK_AST_AC_MISSN_VARNT</TableName>
<FieldName>EFF_DT</FieldName>
<FieldValue>123</FieldValue>
</Table1>
</NewDataSet>'
EXEC
sp_xml_preparedocument @handle OUTPUT, @xmlDoc
SELECT
* FROM OPENXML (@handle, '/NewDataSet/Table1', 2) WITH (TableName VARCHAR(20), FieldName NVARCHAR(20), FieldValue NVARCHAR(20) )
EXEC
Sp_xml_removedocument @handle

quote:
Originally posted by anujpratap84

Hi All,

I have a xml stream and i want to convert it into a tamporary data table.
<?xml version="1.0" encoding="utf-8" ?>
<DataSet>
<NewDataSet>
<TableName>T_NCO_LNK_AST_AC_MISSN_VARNT</TableName>
<FieldName>EFF_DT</FieldName>
<FieldValue>1233</FieldValue>
</NewDataSet>
<NewDataSet>
<TableName>T_NCO_LNK_AST_AC_MISSN_VARNT1</TableName>
<FieldName>EFF_DT1</FieldName>
<FieldValue>1232</FieldValue>
</NewDataSet>
<NewDataSet>
<TableName>T_NCO_LNK_AST_AC_MISSN_VARNT2</TableName>
<FieldName>EFF_DT2</FieldName>
<FieldValue>1231</FieldValue>
</NewDataSet>
</DataSet>

Below is the table format i want.

TableName FieldName FieldValue

Please help

Anuj Pratap Singh

Anuj Pratap Singh

Anuj Pratap Singh

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-27 : 03:39:02

Great work Anuj

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources