Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
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
Updated 30-Sep-12 8:18am
v3

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]
 
Share this answer
 
v2
Comments
akosidandan 1-Oct-12 0:38am    
Hi,

thanks for the response and link but it seems in the link it specify already the columns wherein what I need to do it dynamically.
Maciej Los 1-Oct-12 15:25pm    
What you mean dynamically? Do you want to read all nodes and values dynamically?
akosidandan 2-Oct-12 2:42am    
Hi,

Thanks again for the response. I can read all nodes and values dynamically from my xml but like in my desired output I need the result of columnheader to be the column and the result of columnval to be the data of the columnheader. I can read the data from xml to my desired output but I need to know the schema of xml wherein my problem as well is that xml schema is not always the same as others so I'm trying if I can manage to read its data dynamically.

My apology if my question is quite confusing :)


Maciej Los 2-Oct-12 14:20pm    
See my answer after update.
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)
 
Share this answer
 
Comments
akosidandan 30-Sep-12 23:41pm    
Hi,
thanks for the response but I need to do it dynamically.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900