extracting data from xlm field using sql

Question

I need help to extract the applicant age from the following example of xlm:

<ScoringLog>

  <ScoringType>TEST</ScoringType>

  <scoreConstant>100</scoreConstant>

  <Score>100</Score>

  <ApplicantAge ApplicantAge="27.00" Score="0" />

 </ScoringLog>

The code below pulls the scoring type, scoreconstant & score but the applicantage will not work.  I cannot for the life of me figure out how to get this data.  Any help would be appreciated.

SELECT

R.bau.value ('* [1]', 'nvarchar(20)') as [scoringtype],

R.bau.value ('* [2] ', 'int') as [scoreConstant],

R.bau.value ('* [3] ', 'int') as [Score],

R.bau.value ('* [4] ', 'nvarchar(50)') as [ApplicantAge]

FROM   CreditDecision.dbo.XmlLog a

  inner join  [CreditDecision].[dbo].[ScoreXmlJunct] b

  on b.XmlLogID=a.XmlLogID

  CROSS APPLY Body.nodes ('//ScoringLog') R(bau)

Answer 1

I wouldn't use *, try this instead:

DECLARE @Xml XML = '
<ScoringLog>
  <ScoringType>TEST</ScoringType>
  <scoreConstant>100</scoreConstant>
  <Score>100</Score>
  <ApplicantAge ApplicantAge="27.00" Score="0" />
 </ScoringLog>
';

SELECT  R.bau.value('ScoringType[1]', 'nvarchar(20)') AS [scoringtype] ,
        R.bau.value('scoreConstant[1]', 'int') AS [scoreConstant] ,
        R.bau.value('Score[1]', 'int') AS [Score] ,
        R.bau.value('ApplicantAge[1]/@ApplicantAge', 'nvarchar(50)') AS [ApplicantAge] ,
        R.bau.value('ApplicantAge[1]/@Score', 'nvarchar(50)') AS [ApplicantScore]
FROM    @Xml.nodes('/ScoringLog') R ( bau );

Answer 2

Thankyou so much for your help.  Just needed to add in the database/table where all the xml data is stored and now Im able to pull the data I need.  I am going through a steep learning curve with this XML stuff!