Click here to Skip to main content
13,591,489 members
Rate this:
Please Sign up or sign in to vote.
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.
declare @foo as xml
set @foo=N'

    bar.value('local-name(.)','varchar(20)') as ColumnHeader,
    bar.value('(./.)','varchar(20)') as columnVal
    @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.

Posted 30-Sep-12 4:50am
Updated 30-Sep-12 8:18am
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Have a look at this:[^]

I hope it will be helpful...

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:[^][^][^][^][^][^][^][^][^]

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>

EXEC sp_xml_preparedocument @iDoc OUTPUT, @foo

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

EXEC sp_xml_removedocument @iDoc

akosidandan 1-Oct-12 0:38am

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

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.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Can you not:

declare @foo as xml
set @foo=N'

  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)
akosidandan 30-Sep-12 23:41pm
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
Top Experts
Last 24hrsThis month

Advertise | Privacy | Cookies | Terms of Service
Web03-2016 | 2.8.180618.1 | Last Updated 2 Oct 2012
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

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