Get values from xml variable in a single column

Question

Try to get values from xml variable in single column from CCC tag, num attribute.

DECLARE @XML XML = '
<FileId global_id="1234">
  <file id="12bb"><vd>3</vd>
    <pl_b type_b ="222" k="222" name="bbb"></pl_b>
    <period from="2019-04-01" to="2019-06-30"></period>
    <all>2</all>
    <CCC num = "123" />
    <CCC num = "444" />
  </file>
</FileId>'

Expected Result:
|num|
|---|
|123|
|444|

Now I can get only one value:

SELECT
    F.[File].value(N'(CCC/@num)[1]', 'nvarchar(100)') as acc
FROM (VALUES (@XML)) V (X)
     CROSS APPLY V.X.nodes('/FileId/file') F([File]);

The problem is it's unknown how many values can be get from xml. In example 2 values also may be 2 two thousand. Second question is how can extract how many values in CCC tag, num attribute?

Thom A · Accepted Answer · 2019-12-23 11:46:53Z

2

"Now I can get only one value:" because you're only asking for the first value (CCC/@num)[1]. This means the first node for CCC/@Num, which would be 123.

You need to include the CCC node in your CROSS APPLY:

SELECT F.CCC.value(N'(@num)[1]', 'int') AS acc --I assume int is actually the correct datatype
FROM (VALUES (@XML)) V (X)
     CROSS APPLY V.X.nodes('/FileId/file/CCC') F([CCC]);

answered Dec 23, 2019 at 11:46

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yitzhak Khabinsky · Accepted Answer · 2019-12-23 16:37:30Z

1

There is a much more performant way without CROSS APPLY. I put Query cost in the comments for comparison.

SQL

DECLARE @xml XML = '
<FileId global_id="1234">
  <file id="12bb"><vd>3</vd>
    <pl_b type_b ="222" k="222" name="bbb"></pl_b>
    <period from="2019-04-01" to="2019-06-30"></period>
    <all>2</all>
    <CCC num = "123" />
    <CCC num = "444" />
  </file>
</FileId>';

-- Method #1
-- Query cost 97%
SELECT F.CCC.value(N'(@num)[1]', 'int') AS acc --I assume int is actually the correct datatype
FROM (VALUES (@XML)) V (X)
     CROSS APPLY V.X.nodes('/FileId/file/CCC') F([CCC]);

-- Method #2
-- Query cost 3%
SELECT c.value('.', 'INT') AS num
FROM @xml.nodes('/FileId/file/CCC/@num') t(c);

answered Dec 23, 2019 at 16:37

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

Collectives™ on Stack Overflow

Get values from xml variable in a single column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related