SQL Server .nodes()按名称筛选XML父节点

Question

    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)

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

我该如何按名称（“solutionnumber”）检查父节点，直到找到它？谢谢任何帮助。

- duffn

---

我的术语错误。 我仍然在寻找“solutionnumber”，然而。 - duffn

Answer 1

据我所知，没有直接的方法。但是，您可以使用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 )

请注意，<solutionnumber>实际上是祖先的一个同级而不是祖先本身。

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

---

如果您更愿意向前而不是向后，也可以使用此解决方案：

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 )

它利用了<solutionnumber>标签是<solution>标签的直接子节点这一事实。首先找到所有的<solution>标签。然后使用交叉应用程序查找其所有标题后代。由于不能在节点上使用节点函数，因此在两者之间进行了“query（'.'）”的计算。

除了上面的解决方案，这个解决方案可以处理<solution>标签和<title>标签之间的任何距离。

- Sebastian Meine

Answer 2

也许我之前的做法是错误的。多次交叉应用可以完成任务。感谢在另一个论坛上得到的帮助。

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)

- duffn

Answer 3

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);

- RichardTheKiwi

Answer 4

这将完美地运行...

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)

- Venkat Gali