如何从xml的动态字段动态地旋转表 [英] How to pivot table dyanamically from dynamic fields of xml

问题描述

您好，专家，

我想问一下如何动态提取从xml文件中提取的结果.

以下是从XML文件动态获取结果的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

我不是专门询问代码，但是我想问一下如何做到这一点，因为我已经阅读了通常使用聚合的数据透视表.
任何帮助将不胜感激.

谢谢，
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

推荐答案

看看这个: CTE [ http://msdn.microsoft.com/en-us/library/ms345137.aspx [ ^ ]
http://msdn.microsoft.com/zh-CN/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://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/论坛/en-ZA/transactsql/thread/31e3b14b-862a-4067-9ece-0338015e4fb5 [

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]

[/UPDATE]

您不能:

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)

这篇关于如何从xml的动态字段动态地旋转表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！