| Author |
 Topic  |
|
|
anujpratap84
Starting Member
India
45 Posts |
 Posted - 04/26/2012 : 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
India
45 Posts |
Posted - 04/26/2012 : 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
India
45 Posts |
Posted - 04/26/2012 : 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
India
227 Posts |
Posted - 04/27/2012 : 03:39:02
|
Great work Anuj
N 28° 33' 11.93148"
E 77° 14' 33.66384" |
|
|
| |
Topic |
|
xml to datatable
