CODEWITHSUNDEEP
sql-serverxml
Matthew Sleight
Matthew Sleight

Reputation: 23

XML to SQL Server Code Issue

I have an issue with understanding how to query values from XML nodes within SQL server. I have a very large and complicated XML file that I'm trying to read, I've found several sites that offer advice for specific cases, and while some work in simpler versions of my problem, none work with the file itself.

The following code is some code that recreates a part of the XML file for me to use to learn the syntax, and it works:

DECLARE @XML AS XML


set @XML = '
                                    <creditscores>
                                        <creditscore>
                                            <score class="10">571</score>
                                            <reasons>
                                                <code>506</code>
                                                <code>115</code>
                                                <code>116</code>
                                                <code>104</code>
                                            </reasons>
                                        </creditscore>
                                        <creditscore>
                                            <score class="2">586</score>
                                            <reasons>
                                                <code>105</code>
                                                <code>302</code>
                                                <code>112</code>
                                                <code>104</code>
                                            </reasons>
                                        </creditscore>
                                    </creditscores>
'

select @XML
;

--Credit Score Reason Codes
select
i.x.value('text()[1]', 'int') as [Code]
from @XML.nodes('/creditscores/creditscore/reasons/code') as i(x)

All good so far, I get an output of eight rows, but now I'm wanting to capture the two 'score's in a separate column, and also catch the 'class' of the score.

My output should be three columns: 

class, score, code;
10,    571,   506;
10,    571,   115;
10,    571,   116;
10,    571,   104;
2,     586,   105;
2,     586,   302;
2,     586,   112;
2,     586,   104;

Thanks in advance, I've not found an example of someone asking for anything similar in my searches.

Edit: Added expected output

Edit 2: I have now worked out how to get the 'class' but I cant get it at the same time as the Code, only the Score. I used this query:

--Credit Scores
select
i.scores.value('@class', 'int') as Class,
i.scores.value('text()[1]', 'int') as Score
from @XML.nodes('/creditscores/creditscore/score') as i(scores)

That gives me the following output:

Class,  Score;
10,     571;
2,      586;

Upvotes: 2

Views: 52

Answers (1)

Thom A
Thom A

Reputation: 95554

You'll need to add another layer in your FROM to the other node. You can do this by using CROSS APPLY. According that reasons is also at the same layer as score, then one way would be:

SELECT i.scores.value('@class', 'int') as Class,
       i.scores.value('text()[1]', 'int') as Score,
       s.code.value('text()[1]','int') AS Code
FROM @XML.nodes('/creditscores/creditscore/score') AS i(scores)
     CROSS APPLY i.scores.nodes('../reasons/code') AS s(code);

Personally, however, I would wruite the query this way:

SELECT cs.score.value('@class', 'int') as Class,
       cs.score.value('text()[1]', 'int') as Score,
       cr.code.value('text()[1]','int') AS Code
FROM @XML.nodes('/creditscores/creditscore') AS i(creditscore)
     CROSS APPLY i.creditscore.nodes('score') AS cs(score)
     CROSS APPLY i.creditscore.nodes('reasons/code') AS cr(code);

Upvotes: 1

Related Questions