How do I get a value from XML column in SQL?

So, I have a table with a large chunk of data stored in XML.

The partial XML schema (down to where I need) looks like this:

<DecisionData>
  <Customer>
    <SalesAttemptNumber />
    <SubLenderID>IN101_CNAC</SubLenderID>
    <DecisionType>Decision</DecisionType>
    <DealerID />
    <CustomerNumber>468195994772076</CustomerNumber>
    <CustomerId />
    <ApplicationType>Personal</ApplicationType>
    <ApplicationDate>9/16/2008 11:32:07 AM</ApplicationDate>
    <Applicants>
      <Applicant PersonType="Applicant">
        <CustNum />
        <CustomerSSN>999999999</CustomerSSN>
        <CustLastName>BRAND</CustLastName>
        <CustFirstName>ELIZABETH</CustFirstName>
        <CustMiddleName />
        <NumberOfDependants>0</NumberOfDependants>
        <MaritalStatus>Single</MaritalStatus>
        <DateOfBirth>1/1/1911</DateOfBirth>
        <MilitaryRank />
        <CurrentAddress>
          <ZipCode>46617</ZipCode>

Unfortunately, I am unfamiliar with pulling from XML, and my google-fu has failed me.

 select TransformedXML.value('(/DecisionData/Customer/Applicants/Applicant PersonType="Applicant"/CurrentAddress/ZipCode/node())[1]','nvarchar(max)') as zip
from XmlDecisionInputText as t

I believe my problem lies with the portion that goes Applicant PersonType=”Applicant”, but am unsure how to deal with it.

Thanks for any help.

Answer

The xpath in its simplest form would be:

TransformedXML.value('(//ZipCode)[1]', 'nvarchar(100)') AS zip

This will find the first ZipCode node anywhere inside your document. If there are multiple, just be specific (as much as you want but not any more):

TransformedXML.value('(/DecisionData/Customer/Applicants/Applicant[@PersonType="Applicant"]/CurrentAddress/ZipCode)[1]', 'nvarchar(100)') AS zip

DB Fiddle

Advertisement

Answer