Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: TSQL
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.
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 5:50am
Edited 30-Sep-12 9:18am
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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:
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]
  Permalink  
v2
Comments
akosidandan at 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 at 1-Oct-12 15:25pm
   
What you mean dynamically? Do you want to read all nodes and values dynamically?
akosidandan at 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 at 2-Oct-12 14:20pm
   
See my answer after update.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Can you not:
 
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)
  Permalink  
Comments
akosidandan at 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)

  Print Answers RSS
0 Marcin Kozub 330
1 OriginalGriff 256
2 Sergey Alexandrovich Kryukov 215
3 Praneet Nadkar 197
4 Richard MacCutchan 182
0 OriginalGriff 8,048
1 Sergey Alexandrovich Kryukov 7,287
2 DamithSL 5,614
3 Manas Bhardwaj 4,986
4 Maciej Los 4,910


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 2 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100