sql-serverxmlt-sqlxqueryshred

Extracting a value from an XML column in SQL Server

I am trying to extract the data values present in the FirstName and LastName from the below XML which is present as a string in a column in a SQL Server table. 

<ns6:Account xmlns="http://example.com/pc/gx/abc.pc.dm.gx.shared.contact.contactmodel" xmlns:ns2="http://example.com/pc/gx/abc.pc.dm.gx.shared.location.addressmodel" xmlns:ns4="http://example.com/pc/gx/abc.pc.dm.gx.shared.contact.officialidmodel" xmlns:ns3="http://example.com/pc/gx/abc.pc.dm.gx.shared.contact.contactaddressmodel" xmlns:ns6="http://example.com/pc/gx/abc.pc.dm.gx.base.account.abc" xmlns:ns5="http://example.com/pc/gx/abc.pc.dm.gx.shared.contact.accountcontactmodel" xmlns:ns8="http://example.com/pc/gx/abc.pc.dm.gx.shared.general.usermodel" xmlns:ns7="http://example.com/pc/gx/abc.pc.dm.gx.shared.general.historymodel" xmlns:ns13="http://example.com/pc/gx/abc.pc.dm.gx.base.account.abc" xmlns:ns9="http://example.com/pc/gx/abc.pc.dm.gx.shared.general.activitymodel" xmlns:ns12="http://example.com/pc/gx/abc.pc.dm.gx.shared.general.industrycodemodel" xmlns:ns11="http://example.com/pc/gx/abc.pc.dm.gx.shared.general.documentmodel" xmlns:ns10="http://example.com/pc/gx/abc.pc.dm.gx.shared.general.groupmodel" xmlns:ns17="http://example.com/pc/gx/abc.pc.dm.gx.shared.producer.producercodemodel" xmlns:ns16="http://example.com/pc/gx/abc.pc.dm.gx.shared.producer.accountproducercodemodel" xmlns:ns15="http://example.com/pc/gx/abc.pc.dm.gx.shared.general.notemodel" xmlns:ns14="http://example.com/pc/gx/abc.pc.dm.gx.base.account.abc">
    <ns6:AccountHolderContact>
        <entity-Person>
            <DateOfBirth>999-01-02T12:00:00-05:00</DateOfBirth>
            <FirstName>ABC</FirstName>
            <Gender>F</Gender>
            <LastName>ABC</LastName>
            <LicenseNumber>9999-9999-9999</LicenseNumber>
            <LicenseState>AA</LicenseState>
            <MaritalStatus>S</MaritalStatus>
            <OrganizationType_IC>individual</OrganizationType_IC>
        </entity-Person>
        <HomePhone>9999999999</HomePhone>
        <PrimaryAddress>
            <ns2:AddressLine1>99 ABC St</ns2:AddressLine1>
            <ns2:AddressType>home</ns2:AddressType>
            <ns2:City>AAA</ns2:City>
            <ns2:Country>AA</ns2:Country>
            <ns2:PostalCode>ABC MMM</ns2:PostalCode>
            <ns2:State>AA</ns2:State>
            <ns2:Subtype>Address</ns2:Subtype>
        </PrimaryAddress>
        <PublicID>1</PublicID>
        <Subtype>person</Subtype>
    </ns6:AccountHolderContact>
</ns6:Account>

This is the query I tried:

select 
    application_id, accountID, 
    cast(payload as xml).value('(//*:Account//*:AccountHolderContact)[1]', 'varchar(max)') as FirstName
from
    [test1].[dbo].[test2]

This query returns the data in all the sub-nodes from the XML node <AccountHolderContact>.

999-01-02T12:00:00-05:00ABCFABC9999-9999-9999AASIndividual999999999999 ABC SthomeAAAAAABC MMMAAAddress1Person

When I change my query to the below, I get no data in my output column FirstName:

select 
    application_id, accountID, 
    cast(payload as xml).value('(//*:Account//*:AccountHolderContact/entity-Person/FirstName)[1]','varchar(max)') as FirstName
from
    [test1].[dbo].[test2]

Is there a reason why I cannot extract from the sub-nodes of AccountHolderContact? If not, what is the easiest way to do this?

Solution

  • Your XML has multiple namespaces - 17 total. Just two of them should be taken into account. It is better not to use namespace wildcards due to performance reasons.

    Here is how to shred your XML and retrieve what you need.

    SQL

    -- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, payload NVARCHAR(MAX));
INSERT INTO @tbl (payload) VALUES
(N'<?xml version="1.0"?>
<ns6:Account xmlns="http://example.com/pc/gx/abc.pc.dm.gx.shared.contact.contactmodel"
             xmlns:ns2="http://example.com/pc/gx/abc.pc.dm.gx.shared.location.addressmodel"
             xmlns:ns4="http://example.com/pc/gx/abc.pc.dm.gx.shared.contact.officialidmodel"
             xmlns:ns3="http://example.com/pc/gx/abc.pc.dm.gx.shared.contact.contactaddressmodel"
             xmlns:ns6="http://example.com/pc/gx/abc.pc.dm.gx.base.account.abc"
             xmlns:ns5="http://example.com/pc/gx/abc.pc.dm.gx.shared.contact.accountcontactmodel"
             xmlns:ns8="http://example.com/pc/gx/abc.pc.dm.gx.shared.general.usermodel"
             xmlns:ns7="http://example.com/pc/gx/abc.pc.dm.gx.shared.general.historymodel"
             xmlns:ns13="http://example.com/pc/gx/abc.pc.dm.gx.base.account.abc"
             xmlns:ns9="http://example.com/pc/gx/abc.pc.dm.gx.shared.general.activitymodel"
             xmlns:ns12="http://example.com/pc/gx/abc.pc.dm.gx.shared.general.industrycodemodel"
             xmlns:ns11="http://example.com/pc/gx/abc.pc.dm.gx.shared.general.documentmodel"
             xmlns:ns10="http://example.com/pc/gx/abc.pc.dm.gx.shared.general.groupmodel"
             xmlns:ns17="http://example.com/pc/gx/abc.pc.dm.gx.shared.producer.producercodemodel"
             xmlns:ns16="http://example.com/pc/gx/abc.pc.dm.gx.shared.producer.accountproducercodemodel"
             xmlns:ns15="http://example.com/pc/gx/abc.pc.dm.gx.shared.general.notemodel"
             xmlns:ns14="http://example.com/pc/gx/abc.pc.dm.gx.base.account.abc">
    <ns6:AccountHolderContact>
        <entity-Person>
            <DateOfBirth>999-01-02T12:00:00-05:00</DateOfBirth>
            <FirstName>ABC</FirstName>
            <Gender>F</Gender>
            <LastName>ABC</LastName>
            <LicenseNumber>9999-9999-9999</LicenseNumber>
            <LicenseState>AA</LicenseState>
            <MaritalStatus>S</MaritalStatus>
            <OrganizationType_IC>individual</OrganizationType_IC>
        </entity-Person>
        <HomePhone>9999999999</HomePhone>
        <PrimaryAddress>
            <ns2:AddressLine1>99 ABC St</ns2:AddressLine1>
            <ns2:AddressType>home</ns2:AddressType>
            <ns2:City>AAA</ns2:City>
            <ns2:Country>AA</ns2:Country>
            <ns2:PostalCode>ABC MMM</ns2:PostalCode>
            <ns2:State>AA</ns2:State>
            <ns2:Subtype>Address</ns2:Subtype>
        </PrimaryAddress>
        <PublicID>1</PublicID>
        <Subtype>person</Subtype>
    </ns6:AccountHolderContact>
</ns6:Account>');
-- DDL and sample data population, end

;WITH XMLNAMESPACES (DEFAULT 'http://example.com/pc/gx/abc.pc.dm.gx.shared.contact.contactmodel'
    , 'http://example.com/pc/gx/abc.pc.dm.gx.base.account.abc' AS ns14) ,rs AS
(
   SELECT id, TRY_CAST(payload AS XML) AS xmldata
   FROM @tbl
)
SELECT ID
   , c.value('(FirstName/text())[1]','VARCHAR(50)') AS FirstName
   , c.value('(LastName/text())[1]','VARCHAR(50)') AS LastName
FROM rs CROSS APPLY rs.xmldata.nodes('/ns14:Account/ns14:AccountHolderContact/entity-Person') AS t(c);

    Output

    +----+-----------+----------+
| ID | FirstName | LastName |
+----+-----------+----------+
|  1 | ABC       | ABC      |
+----+-----------+----------+