Блог

Работа с XML в SQL Server, учитывая этот XML:

     <A>
        <B>123</B>
        <C>
              <Cs>234</Cs>
              <Cs>345</Cs>
              <Cs>12</Cs>
              <Cs>2346</Cs>
        </Cs>
    </A>
 

Я хотел бы создать результирующий набор, который выглядит следующим образом:

Есть ли какой-нибудь трюк, который может сделать это, не проходя через XML? Дополнительным бонусом будет возможность начать с чего-то другого, кроме корня документа. Вы можете перейти /A/C к этой процедуре, и она будет указывать только пути под этим элементом.

Это один из редких случаев, когда архаика OPENXML() пригодится.

XQuery 3.0 представил реальное решение для такой задачи: fn:path() функция давно, в 2014 году. К сожалению, MS SQL Server поддерживает только подмножество XQuery 1.0

Вернемся на обычную землю.

SQL

 DECLARE @xml XML =
N'<A>
    <B>123</B>
    <C>
            <Cs>234</Cs>
            <Cs>345</Cs>
            <Cs>12</Cs>
            <Cs>2346</Cs>
    </C>
</A>';

DECLARE @DocHandle INT;
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xml;

;WITH rs AS
(
   SELECT * FROM OPENXML(@DocHandle,'/*')
), cte AS
(
   -- anchor
    SELECT id
      ,ParentID
      --, nodetype
      , [text]
      ,CAST(id AS VARCHAR(100)) AS [Path]
      ,CAST('/'   rs.localname AS VARCHAR(1000)) 
        N'[' 
          CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS NVARCHAR)  
          N']' AS [XPath]
    FROM rs
    WHERE ParentID IS NULL
    UNION ALL
    --recursive member
    SELECT t.id
      ,t.ParentID
      --, nodetype = (SELECT nodetype FROM rs WHERE id = t.ParentID)
      , t.[text]
      , CAST(a.[Path]   ','   CAST( t.ID AS VARCHAR(100)) AS VARCHAR(100)) AS [Path]
      , CAST(a.[XPath]   '/'   IIF(t.nodetype = 2, '@', '')
           t.localname AS VARCHAR(1000)) 
           N'['
          TRY_CAST(ROW_NUMBER() OVER(PARTITION BY t.localname ORDER BY (SELECT 1)) AS NVARCHAR)
          N']' AS [XPath]
    FROM rs AS t
      INNER JOIN cte AS a ON t.ParentId = a.id
)
SELECT ID, ParentID, /*nodetype,*/ [Path]
   , REPLACE([XPath],'#text','text()') AS XPath
   , [text] AS [Value]
FROM cte
WHERE [text] IS NOT NULL
    --AND CAST([text] AS VARCHAR(30)) = '12345'
ORDER BY [Path];

EXEC sp_xml_removedocument @DocHandle;
 

Выход

  ---- ---------- ---------- ---------------------------- ------- 
| ID | ParentID |   Path   |           XPath            | Value |
 ---- ---------- ---------- ---------------------------- ------- 
|  8 |        2 |    0,2,8 | /A[1]/B[1]/text()[1]       |   123 |
|  9 |        4 |  0,3,4,9 | /A[1]/C[1]/Cs[1]/text()[1] |   234 |
| 10 |        5 | 0,3,5,10 | /A[1]/C[1]/Cs[2]/text()[1] |   345 |
| 11 |        6 | 0,3,6,11 | /A[1]/C[1]/Cs[3]/text()[1] |    12 |
| 12 |        7 | 0,3,7,12 | /A[1]/C[1]/Cs[4]/text()[1] |  2346 |
 ---- ---------- ---------- ---------------------------- ------- 
 

Вы можете использовать рекурсивный CTE. Вы передаете XML-документ в @xml . Если вам нужно использовать таблицу для получения XML, вы можете использовать CROSS APPLY YourXml.nodes вместо FROM @XML.nodes .

 WITH cte AS (
    SELECT
      xpath = CONCAT(v.name, '[', ROW_NUMBER() OVER (PARTITION BY v.name ORDER BY (SELECT 1)), ']'),
      value = x.nd.value('text()[1]','nvarchar(100)'),
      child = x.nd.query('*')
    FROM @xml.nodes('*') x(nd)
    CROSS APPLY (VALUES (x.nd.value('local-name(.)[1]','nvarchar(max)'))) v(name)
    UNION ALL
    SELECT
      xpath = CONCAT(cte.xpath, '/', v.name, '[', ROW_NUMBER() OVER (PARTITION BY xpath, v.name ORDER BY (SELECT 1)), ']'),
      value = x.nd.value('text()[1]','nvarchar(100)'),
      child = x.nd.query('*')
    FROM cte
    CROSS APPLY cte.child.nodes('*') x(nd)
    CROSS APPLY (VALUES (x.nd.value('local-name(.)[1]','nvarchar(max)'))) v(name)
)
SELECT
  xpath = CONCAT('/', xpath, '/text()[1]'),
  value
FROM cte
WHERE value IS NOT NULL;
 

db<>скрипка

К сожалению, вы не можете использовать ancestor:: ось, что сделало бы это намного проще.

Если бы SQL Server поддерживался ancestor:: , вы могли бы сделать что-то вроде этого

 SELECT
  xpath = '('   x.nd.query('for $n in ancestor::* return concat("/", local-name($n))')   '/text())[1]',
  value = x.nd.value('text()[1]','nvarchar(100)')
FROM @xml.nodes('//*[text()]') x(nd)