Skip to content

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:

    <SalesAttemptNumber />
    <DealerID />
    <CustomerId />
    <ApplicationDate>9/16/2008 11:32:07 AM</ApplicationDate>
      <Applicant PersonType="Applicant">
        <CustNum />
        <CustMiddleName />
        <MilitaryRank />

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.



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

4 People found this is helpful