How do I extract a value from an XML column in SQL Server

I have a column SCORERESULTS in table CSE_ARCHIVEDCREDITSCORE.

Score results is an XML column.

<ScoringEngine>
  <Profile id="Navigation" version="1" num="4" uniqueId="8bcf8a8b9efc4e5dad1d87510cfe6a64">
.
.
.
    <Tool id="Payment To Income Ratio" version="1" old_id="Pmt_To_Income">
      <Rule id="PaymentIncomeRatio" version="1" old_id="PTI">
        <Row uniqueId="0fb11598c4224e4c97cf2afcc4e34b54" order="6" id="0">
          <Column order="1" op="RNG2" start="0.18" end="0.2" title="Payment To Income Ratio">0.190325139</Column>
          <Action name="Record Value" value="1.42085235920852" fieldName="LO_R_PMT_TO_INCOME" />
        </Row>
      </Rule>
      <RecordedValue>
        <Value value="1.42085235920852" fieldName="LO_R_PMT_TO_INCOME" />
      </RecordedValue>
    </Tool>
    <Tool id="RecentLoans" version="2">
      <Rule id="RecentLoans" version="2" old_id="RecentLoans" />
    </Tool>
.
.
.
  </Profile>
</ScoringEngine>

I am trying to get the value 0.190325139 out of the XML line: <Column order="1" op="RNG2" start="0.18" end="0.2" title="Payment To Income Ratio">0.190325139</Column> I am clueless on how to pull it. I’m not familiar with XML, or really how to navigate it well.

Answer

You can use the below query. Make sure the column SCORERESULTS is of type XML.

SELECT SCORERESULTS.value('(ScoringEngine/Profile/Tool/Rule/Row/Column)[1]', 'nvarchar(MAX)') AS result
FROM CSE_ARCHIVEDCREDITSCORE

If the column is not of type XML use the below query

SELECT CAST(SCORERESULTS AS XML).value('(ScoringEngine/Profile/Tool/Rule/Row/Column)[1]', 'nvarchar(MAX)') AS result
FROM CSE_ARCHIVEDCREDITSCORE

Advertisement

Answer