T-SQL 将 XML 字段转换为多列数据集 [英] T-SQL Convert XML field to multi-column dataset

问题描述

我想将 XML 字段拆分"为多列数据集.XML 看起来像:

I want to 'split' XML field to the multiple columns dataset. XML looking like:

<simple>
    <propertyid>
        <value>p1</value>
        <value>p2</value>
        <value>p3</value>
        <value>p4</value>
    </propertyid>
    <complexid>
        <value>c1</value>
        <value>c2</value>
        <value>c3</value>
    </complexid>
</simple>

我尝试做类似的事情(每次在 TSQL 中使用 XML 时我都觉得很烦，所以我的代码是错误的):

I tried to do something like that (each time I working with XML in TSQL I feel butthurt, so my code is wrong):

;WITH source AS (
    SELECT CAST(@xmlstring AS XML) AS x
)
SELECT 
    items.item.query('.')
FROM source
CROSS APPLY x.nodes('/simple/*/value') AS items(item)

预期数据集:

ColumnName      Value
------------------------
propertyid      p1
propertyid      p2
propertyid      p3
propertyid      p4
complexid       c1
complexid       c2
complexid       c3

我怎样才能得到要求的结果?也许你可以推荐一些很好的资源，包括 T-SQL + XML + XQuery 解释和示例?

How can I get required result? Maybe you can recommend good resource with T-SQL + XML + XQuery explanations and samples?

推荐答案

这是我自己的 1 CROSS APPLY 解决方案:

And here is my own solution with 1 CROSS APPLY:

;WITH source AS (
    SELECT CAST(@xmlstring AS XML) AS x
)
SELECT
    items.item.value('local-name(..)', 'varchar(300)') AS ColumnName,
    items.item.value('text()[1]', 'varchar') AS Value
FROM source
CROSS APPLY x.nodes('/simple/*/value') AS items(item)

主要是我访问父节点失败，我尝试使用'../local-name()'，但是local-name()需要XPath来显示名称.之后显示父节点名称变得容易.

Mainly I failed to access parent node, I tried to user '../local-name()', but local-name() requires XPath to diplay name. After that show parents node name becomes easy.

这篇关于T-SQL 将 XML 字段转换为多列数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！