CODEWITHSUNDEEP
xmlt-sql
Tom Kev
Tom Kev

Reputation: 146

Get values from xml variable in a single column

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?

Upvotes: 0

Views: 48

Answers (2)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22311

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

Upvotes: 1

Thom A
Thom A

Reputation: 95989

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

Upvotes: 2

Related Questions