Dynamic XML to table - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Dynamic XML to table

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-03-11 : 08:20:31

Hi,
Need to Convert XML to table. Were as my XML columns will be unknown. Please help me to fix this.

@XML:
'<NewDataSet>
<Table1>
<ID>1</ID>
<Name>Kalai</Name>
<PARAM1>9999.00</PARAM1>
<PARAM2>10000.00</PARAM2>
<PARAM3>10001.00</PARAM3>
<INVESTMENT>50005.00</INVESTMENT>
</Table1>
<Table1>
<ID>2</ID>
<Name>Kamal</Name>
<PARAM1>10049.00</PARAM1>
<PARAM2>10050.00</PARAM2>
<PARAM3>10051.00</PARAM3>
<INVESTMENT>50255.00</INVESTMENT>
</Table1>
</NewDataSet>'

Expected Table:
[ID] [Name] [PARAM1] [PARAM2] [PARAM3] [INVESTMENT]
1 Kalai 9999.00 10000.00 10001.00 50005.00
2 Kamal 10049.00 10050.00 10051.00 50255.00

Query:
SELECT
mem.data.value('ID[1]','INT)') AS ID,
mem.data.value('Name[1]','VARCHAR)') AS Name,
-- For PARAM1,PARAM2,PARAM3,PARAM4,PARAM5 ?
mem.data.value('INVESTMENT[1]','NUMERIC(18,2))') AS INVESTMENT
FROM
@XML.nodes('/NewDataSet/Table1')mem(data);

How can i get the Column names for PARAM1 to PARAM5 values. Since its all are dynamic columns. Have tried with Dynamic query with XML. But it exceeds the limit.
Bcoz my XML will have nearly 1000 rows and hence XML file will be large it doesnt supports VARCHAR(MAX).
Plase help me to fix this issue, abt how to get dynamic XML into table in SQL server.

Regards,
Kalai

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-11 : 09:15:06

You can use the local-name function (see here: http://msdn.microsoft.com/en-us/library/ms190650.aspx)

In your example, if you wanted to find the node name of PARAM1 which is the third node in the first Table1 element, you would query it like this:

SELECT @XML.query('local-name((//Table1)[1]/*[3])')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources