SQL Server How to use "cross apply" when parsing xml?

vojdkbi0 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(110)

Trying to parse multiple xml files. But stack in applying cross apply

First variant works fine

DECLARE @input XML = 

'<a>
    <b>
     <g>one</g>
    <n>
        <c>somedata1 1</c>
    </n>
    <n>
        <c>2somedata 2</c>
    </n>
</b>
</a>'       

SELECT
g                   = XC.value('(g)[1]', 'varchar(100)'),
i                   = xc1.n1.value ('(c)[1]', 'varchar(100)')
FROM @input.nodes ('a/b') as XT (XC)
cross apply @input.nodes ('//n') as xc1(n1)

second variant fails (returns empty fields) - I know this is becouse absence of <с> nodes

DECLARE @input XML = 
'<a>
    <b>
     <g>one</g>
        </b>
</a>'
SELECT
    g                   = XC.value('(g)[1]', 'varchar(100)'),
    i                   = xc1.n1.value ('(c)[1]', 'varchar(100)')
FROM @input.nodes ('a/b') as XT (XC)
cross apply @input.nodes ('//n') as xc1(n1)

I working with multiple files, and some of them have those nodes and some no, what is my decision supposed to be?

sql-server

来源：https://stackoverflow.com/questions/36446947/how-to-use-cross-apply-when-parsing-xml

1条答案

按热度按时间

8dtrkrch1#

In second case you don't have any n element. You need to use OUTER APPLY :
There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.

DECLARE @input XML = 
'<a>
    <b>
     <g>one</g>
        </b>
</a>';

SELECT g = XC.value('(g)[1]', 'varchar(100)'),
       i = xc1.n1.value ('(c)[1]', 'varchar(100)')
FROM @input.nodes ('a/b') AS XT (XC)
OUTER APPLY @input.nodes ('//n') AS xc1(n1);

LiveDemo

Output:

╔═════╦══════╗
║  g  ║  i   ║
╠═════╬══════╣
║ one ║ NULL ║
╚═════╩══════╝

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server How to use "cross apply" when parsing xml?

1条答案

相关问题

热门标签

最新问答