SQL Server .nodes() XML父节点的名称

    declare @xmlsample xml =
'<root>
    <solution>
        <solutionnumber>1</solutionnumber>
            <productgroup>
                <productcategory>
                    <price>100</price>
                    <title>Some product</title>
                    <tax>1</tax>
                </productcategory>
            </productgroup>
            <productcategory2>
                    <price>200</price>
                    <title>Some other product</title>
                    <tax>2</tax>
            </productcategory2>
    </solution>
    <solution>
        <solutionnumber>2</solutionnumber>
            <productcategory2>
                    <price>200</price>
                    <title>Some other product</title>
                    <tax>2</tax>
            </productcategory2>
    </solution>
</root>'

SELECT 
    --T.C.value('(./ancestor::ns1:solutionNumber)[1]', 'varchar(50)') AS solutionnumber ?? no clue
    T.C.value('(price)[1]', 'numeric(18,2)') AS price
    ,T.C.value('(title)[1]', 'varchar(50)') AS title
    ,T.C.value('(tax)[1]', 'numeric(18,2)') AS tax
FROM @xmlsample.nodes('//node()[title]') AS T(C)

A representation of the XML I am attempting to shred in SQL Server 2008 r2. I find the "title" node and grab the values I need that are in the product category. Now I would like to get the "solution number" however this could be one or more parent nodes above the product as there are certain product "groups."

我试图在SQL Server 2008 r2中分解的XML的表示。找到“title”节点并获取产品类别中需要的值。现在我想要得到“解决方案编号”，但是这可能是产品上面的一个或多个父节点，因为有特定的产品“组”。

How would I go about check the parent nodes by name ("solutionnumber") until I find it? Thanks for any assistance.

如何通过名称(“solutionnumber”)检查父节点直到找到它?感谢任何帮助。

4 个解决方案

#1

There is no direct way to my knowledge. However, you can use COALESCE to search your way up:

据我所知没有捷径可走。但是，您可以使用COALESCE查找您的路径:

SELECT
    COALESCE(T.C.value('../solutionnumber[1]', 'INT'),
             T.C.value('../../solutionnumber[1]', 'INT'),
             T.C.value('../../../solutionnumber[1]', 'INT')) solutionnumber,
    T.C.value('(price)[1]', 'numeric(18,2)') AS price,
    T.C.value('(title)[1]', 'varchar(50)') AS title,
    T.C.value('(tax)[1]', 'numeric(18,2)') AS tax
  FROM
    @xmlsample.nodes('//node()[title]') AS T ( C )

Note that <solutionnumber> is really a sibling of one of the ancestors and not an ancestor itself.

注意，实际上是一个祖先的兄弟姐妹，而不是一个祖先本身。

This solution requires you to know the maximum depth ahead of time.

这个解决方案要求您提前知道最大深度。

You can also use this solution, if you rather go forward than backwards:

你也可以使用这个解决方案，如果你愿意向前走而不是向后走:

SELECT solutionNodes.solutionNode.value('solutionnumber[1]','INT') AS solutionnumber,
    T.C.value('(price)[1]', 'numeric(18,2)') AS price,
    T.C.value('(title)[1]', 'varchar(50)') AS title,
    T.C.value('(tax)[1]', 'numeric(18,2)') AS tax
FROM @xmlsample.nodes('//solution') AS solutionNodes (solutionNode)
CROSS APPLY (SELECT solutionNodes.solutionNode.query('.')) solutions(solutionXML)
CROSS APPLY solutions.solutionXML.nodes('//node()[title]') T ( C )

It uses the fact that the <solutionnumber> tag is a direct child of a <solution> tag. First all <solution> tags are found. Than all its title descendants are found with a cross apply. Because you cannot use the nodes function on a node there is the calculation of "query('.')" in between.

它使用的事实是标签是标签的直接子标签。首先找到所有标签。比它所有的标题后代被发现与一个交叉应用。因为不能在节点上使用节点函数，所以在两者之间要计算“query('.')”。

Other than above solution, this one can handle any distance between the <solution> tag abd the <title> tag.

除了上面的解决方案，这个解决方案可以处理标记abd和标记之间的任何距离。</p>

#2

Perhaps I was going about this backwards. Multiple cross applies will do the job. Thanks to some assistance on another forum.

也许我是在倒叙。多重交叉将完成这项工作。感谢另一个论坛的一些帮助。

SELECT 
    --T.C.value('(./ancestor::ns1:solutionNumber)[1]', 'varchar(50)') AS solutionnumber ?? no clue
    m.c.value('(solutionnumber)[1]', 'int') as solutionnumber
    ,T.C.value('(price)[1]', 'numeric(18,2)') AS price
    ,T.C.value('(title)[1]', 'varchar(50)') AS title
    ,T.C.value('(tax)[1]', 'numeric(18,2)') AS tax
FROM  @xmlsample.nodes ('//solution') as m (c)
cross apply m.c.nodes ('.//node()[title]') as t(C)

#3

SQL Server does not support going backwards to the ancestor, so here's a roundabout way of caching the pointer to an ancestor while descending into the XML.

SQL Server不支持返回到祖先，所以这里有一种迂回的方法，可以在下降到XML时缓存指向祖先的指针。

declare @xmlsample xml =
'<root>
    <solution>
        <solutionnumber>1</solutionnumber>
            <productgroup>
                <productcategory>
                    <price>100</price>
                    <title>Some product</title>
                    <tax>1</tax>
                </productcategory>
            </productgroup>
            <productcategory2>
                    <price>200</price>
                    <title>Some other product</title>
                    <tax>2</tax>
            </productcategory2>
    </solution>
    <solution>
        <solutionnumber>2</solutionnumber>
            <productcategory2>
                    <price>200</price>
                    <title>Some other product</title>
                    <tax>2</tax>
            </productcategory2>
    </solution>
</root>';

WITH Xml_CTE AS
(
     SELECT node.query('*') AS children,
            node.value('fn:local-name(.)','varchar(100)') localName,
            node.exist('title') IsTitleParent,
            CAST(null as xml) as solution
       FROM @xmlsample.nodes('/*') AS root(node)
  UNION ALL
     SELECT node.query('*') AS children,
            node.value('fn:local-name(.)','varchar(100)') localName,
            node.exist('title') IsTitleParent,
            CASE WHEN node.value('fn:local-name(.)', 'varchar(100)') = 'solution'
                 THEN node.query('.')
                 ELSE solution END
       FROM Xml_CTE x
CROSS APPLY x.children.nodes('*') AS child(node)
)
SELECT solution.value('(solution/solutionnumber/text())[1]', 'int') solutionNumber
      ,children.value('(price)[1]', 'numeric(18,2)') price
      ,children.value('(title)[1]', 'varchar(50)') title
      ,children.value('(tax)[1]', 'numeric(18,2)') tax
  FROM Xml_CTE
 WHERE IsTitleParent = 1 -- matches .nodes('//node()[title]')
OPTION (MAXRECURSION 0);

#4

This will work perfectly...

这将工作完美……

Declare @SomeXML XML
SET @SomeXML = '<SomeValue>GGGG</SomeValue><SomeValue>MMMM</SomeValue><SomeValue>AAA</SomeValue>'

select ROW_NUMBER() over ( order by b), b.value('.', 'varchar(50)')
from @SomeXML.nodes('(/SomeValue)') AS a(b)

#1