How to pivot table dyanamically from dynamic fields of xml

Question

0.00/5 (No votes)

See more:

Hi experts,

I would like to ask how would I pivot dynamically the result I got from extracting an xml file.

Below is the TSQL code that dynamically get result from an xml file.

SQL

declare @foo as xml
set @foo=N'
<Transactions>
    <AccountNo>123456789</AccountNo>
    <Name>dan</Name>
</Transactions>'


select
    bar.value('local-name(.)','varchar(20)') as ColumnHeader,
    bar.value('(./.)','varchar(20)') as columnVal
from
    @foo.nodes('*/*') as foo(bar)

Above code produce this output

Columnheader   | ColumnVal
--------------------------
 AccountNo     |123456789
 Name          |  dan

Wherein my desired output will be

AccountNo   |   Name
---------------------
123456789   |   dan

I'm not specifically asking on code but I would like to ask how will I managed to do that because I have read that pivot table usually used aggregate.
Anyhelp would be appreciated.

Thanks,
Dan

Posted 30-Sep-12 4:50am

akosidandan

Updated 30-Sep-12 8:18am

Sandeep Mewara

v3

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2012-09-30T10:40:00

Have a look at this: http://stackoverflow.com/questions/192398/select-xml-nodes-as-rows[^]

I hope it will be helpful...

[UPDATE]
I think, the fully dynamic way is not possible, because each XML document has different structure, stores different data, values... So, you can't 'pivot' (transpose) rows as columns (ColumnHeader) and its values (ColumnVal) as rows. Maybe CTE[^] technology would be helpful, but i'm not sure that CTE can produce columns based on the values of rows.

More about XML queries, you'll find at:
http://msdn.microsoft.com/en-us/library/ms345137.aspx[^]
http://msdn.microsoft.com/en-US/library/ms345115%28v=sql.90%29.aspx[^]
http://sqlwithmanoj.wordpress.com/tag/cross-apply/[^]
http://www.sqlservercentral.com/Forums/Topic595669-338-1.aspx#bm596354[^]
http://beyondrelational.com/modules/2/blogs/28/posts/10503/xquery-lab-61-writing-a-recursive-cte-to-process-an-xml-document.aspx[^]
http://geekswithblogs.net/DougLampe/archive/2011/03/16/select-data-from-xml-in-ms-sql-server-t-sql.aspx[^]
http://stackoverflow.com/questions/192398/select-xml-nodes-as-rows[^]
http://www.4guysfromrolla.com/webtech/071906-1.shtml[^]
http://social.msdn.microsoft.com/Forums/en-ZA/transactsql/thread/31e3b14b-862a-4067-9ece-0338015e4fb5[^]

If you would like to get data in desired format, please, have a look at this query:

SQL

DECLARE @iDoc INT /* Stores a pointer to the XML document */
DECLARE @foo as xml
set @foo=N'<Transactions>
<Transaction>
    <AccountNo>1234567890</AccountNo>
    <Name>dan</Name>
</Transaction>
<Transaction>
    <AccountNo>2345678901</AccountNo>
    <Name>andy</Name>
</Transaction>
<Transaction>
    <AccountNo>3456789012</AccountNo>
    <Name>nad</Name>
</Transaction>
</Transactions>'


EXEC sp_xml_preparedocument @iDoc OUTPUT, @foo

SELECT *
FROM OPENXML(@iDoc,'/Transactions/Transaction',2)
WITH ([TAccountNo] varchar(50)  'AccountNo',                                                    
[TName] varchar(50)  'Name')

EXEC sp_xml_removedocument @iDoc

[/UPDATE]

db7uk · Answer 2 · 2012-09-30T10:43:00

Solution 2

Can you not:

SQL

declare @foo as xml
set @foo=N'
<transactions>
    <accountno>123456789</accountno>
    <name>dan</name>
</transactions>'

SELECT 
  bar.value('AccountNo[1]', 'varchar(20)') AS AccountNo ,
  bar.value('Name[1]', 'varchar (20)') AS Name
FROM @foo.nodes('/Transactions') 
xmlData( bar )

(Or have I missed something)

Posted 30-Sep-12 10:43am

db7uk

Comments

akosidandan 30-Sep-12 23:41pm

Hi,
thanks for the response but I need to do it dynamically.

How to pivot table dyanamically from dynamic fields of xml

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How to pivot table dyanamically from dynamic fields of xml

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us