将XML字段转换为多列数据集的T-SQL转换

Question

我希望将XML字段拆分为多列数据集。 XML样式如下：

<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时，我都感到痛苦，所以我的代码是错误的）：

;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解释和示例的好资源？

- Alex G.P.

Answer 1

尝试像这样做：

尝试像这样做：

;WITH source AS (
    SELECT CAST(@xmlstring AS XML) AS x
)
SELECT
    ColumnName = XTbl.Parent.value('local-name(.)', 'varchar(50)'),
    [Value] = XTbl2.ChildValues.value('(.)[1]', 'varchar(20)')
FROM source
CROSS APPLY
    x.nodes('/simple/*') AS XTbl(Parent)
CROSS APPLY
    XTbl.Parent.nodes('value') AS XTbl2(ChildValues)

这对你有用吗？在我的情况下（使用辅助表），我得到了期望的输出。

查看此SQL Fiddle演示

- marc_s

Answer 2

以下是我自己的解决方案，使用了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来显示名称。之后显示父节点的名称就变得容易了。

- Alex G.P.

Answer 3

根据你的要求，这里提供一个关于XQuery解决方案的建议：

declare function local:createTestDoc() as node()* {
    <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>
};

declare function local:doProcessing($arg as node()*) as xs:string* {
    let $simpleElement := $arg

    return (
        concat("ColumnName", "    ", "Value")
        ,
        for $propertyId in $simpleElement/propertyid/*
        return (
            concat("propertyid", "    ", data($propertyId))
        )
        ,
        for $complexId in $simpleElement/complexid/*
        return (
            concat("complexid", "    ", data($complexId))
        )
    )
};

local:doProcessing(local:createTestDoc())

不要使用local:createTestDoc()，而是要使用类似以下的方式来包含您的文档：

declare variable $myDoc := doc("urlToDoc");

将其传递给local:doProcessing()函数。

local:doProcessing($myDoc)

- Will C.