sql-server - 使用 TSQL 解析 XML - 带有嵌套节点问题的 value()

Question

我正在使用 TSQL 解析 xml 文件以构建一个表以供进一步分析。使用来自 xquery-lab-61-writing-a-recursive-cte-to-process-an-xml-document 的伟大建议我使用 CTE 但没有得到想要的结果。问题在于带有子节点的 value() 函数。

我有

DECLARE @x XML  
SELECT @x = '
<books>
    <book id="101">
        <title>my book</title>
        <author>Myself</author>
    </book>
    <book id="202">
        text before
          <title>your book</title>
        in the middle
          <author>you</author>
        text after
    </book>
</books>'

;WITH cte AS ( 
    SELECT 
        1 AS lvl, 
        x.value('local-name(.)','VARCHAR(MAX)') AS FullPath, 
        x.value('text()[1]','VARCHAR(MAX)') AS Value, 
        x.query('.') AS CurrentNode,        
        CAST(CAST(1 AS VARBINARY(4)) AS VARBINARY(MAX)) AS Sort
    FROM @x.nodes('/*') a(x) 
    UNION ALL 
    SELECT 
        p.lvl + 1 AS lvl, 
        CAST( 
            p.FullPath 
            + '/' 
            + c.value('local-name(.)','VARCHAR(MAX)') AS VARCHAR(MAX) 
        ) AS FullPath, 
        CAST( c.value('text()[1]','VARCHAR(MAX)') AS VARCHAR(MAX) ) AS Value, 
        c.query('.')  AS CurrentNode,        
        CAST( 
            p.Sort 
            + CAST( (lvl + 1) * 1024 
            + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS VARBINARY(4) 
        ) AS VARBINARY(MAX) ) AS Sort
    FROM cte p 
    CROSS APPLY CurrentNode.nodes('/*/*') b(c)        
), cte2 AS (
    SELECT 
        FullPath, 
        Value, 
        Sort 
    FROM cte 
    UNION ALL 
    SELECT 
        p.FullPath + '/@' + x.value('local-name(.)','VARCHAR(MAX)'), 
        x.value('.','VARCHAR(MAX)'),
        Sort 
    FROM cte p 
    CROSS APPLY CurrentNode.nodes('/*/@*') a(x) 
)
SELECT FullPath, value 
FROM cte2
WHERE Value IS NOT NULL
ORDER BY Sort 

结果是

FullPath             Value
-------------------- ------------------------------
books\book\@id       101
books\book\title     my book
books\book\author    Myself
books\book           text before 
books\book\@id       202
books\book\title     your book
books\book\author    you

我需要这样的东西:

FullPath             Value
-------------------- ------------------------------
books\book\@id       101
books\book\title     my book
books\book\author    Myself
books\book           text before 
books\book\@id       202
books\book\title     your book
books\book           in the middle
books\book\author    you
books\book           text after

如果可能的话，我更愿意找到使用 TSQL 的解决方案。我将非常感谢任何好的解决方案/建议。

Answer 1

使用 OPENXML 更容易做到这一点而不是使用 XML 数据类型。

使用 OPENXML，您可以创建一个边表，其中一行对应 XML 中的每个节点。

declare @idoc int;
exec sp_xml_preparedocument @idoc out, @x;

select *
from openxml(@idoc, '')

exec sp_xml_removedocument @idoc;

结果:

id  parentid nodetype localname prefix namespaceuri datatype prev text
--- ----------------- --------- ------ ------------ -------- ---- -------------
0   NULL     1        books     NULL   NULL         NULL     NULL NULL
2   0        1        book      NULL   NULL         NULL     NULL NULL
3   2        2        id        NULL   NULL         NULL     NULL NULL
13  3        3        #text     NULL   NULL         NULL     NULL 101
4   2        1        title     NULL   NULL         NULL     NULL NULL
14  4        3        #text     NULL   NULL         NULL     NULL my book
5   2        1        author    NULL   NULL         NULL     4    NULL
15  5        3        #text     NULL   NULL         NULL     NULL Myself
6   0        1        book      NULL   NULL         NULL     2    NULL
7   6        2        id        NULL   NULL         NULL     NULL NULL
16  7        3        #text     NULL   NULL         NULL     NULL 202
8   6        3        #text     NULL   NULL         NULL     NULL text before
9   6        1        title     NULL   NULL         NULL     8    NULL
17  9        3        #text     NULL   NULL         NULL     NULL your book
10  6        3        #text     NULL   NULL         NULL     9    in the middle
11  6        1        author    NULL   NULL         NULL     10   NULL
18  11       3        #text     NULL   NULL         NULL     NULL you
12  6        3        #text     NULL   NULL         NULL     11   text after

将边缘表存储在临时表中，并使用 id 和 parentid 进行递归 CTE。在构建 FullPath 列时使用 nodetype。

declare @x xml;  
select @x = '
<books>
    <book id="101">
        <title>my book</title>
        <author>Myself</author>
    </book>
    <book id="202">
        text before
          <title>your book</title>
        in the middle
          <author>you</author>
        text after
    </book>
</books>';

declare @idoc int;
exec sp_xml_preparedocument @idoc out, @x;

select *
into #T
from openxml(@idoc, '');

exec sp_xml_removedocument @idoc;

with C as
(
  select T.id,
         T.parentid,
         T.localname as FullPath,
         T.text as Value
  from #T as T
  where T.parentid is null
  union all
  select T.id,
         T.parentid,
         C.FullPath + case T.nodetype 
                        when 1 then  N'\' + T.localname  -- Element node
                        when 2 then  N'\@' + T.localname -- Attribute node
                        when 3 then  N''                 -- Text node
                        when 4 then  N''                 -- CDATA secotion node
                        when 5 then  N''                 -- Entity reference node
                        when 6 then  N''                 -- Entity node
                        when 7 then  N''                 -- Processing instrution node
                        when 8 then  N''                 -- Comment node
                        when 9 then  N''                 -- Document node
                        when 10 then N''                 -- Document type node
                        when 11 then N''                 -- Document fragment node
                        when 12 then N''                 -- Notation node
                      end,
         T.text
  from C
    inner join #T as T
      on C.id = T.parentid
)
select C.FullPath,
       C.Value
from C
where C.Value is not null
order by C.parentid, 
         C.id;

drop table #T;

结果:

FullPath           Value
------------------ --------------
books\book\@id     101
books\book\title   my book
books\book\author  Myself
books\book         text before
books\book         in the middle
books\book         text after
books\book\@id     202
books\book\title   your book
books\book\author  you

SQL Fiddle