Extracting a value from an XML column in SQL Server

Question

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.


    
        
            999-01-02T12:00:00-05:00
            ABC
            F
            ABC
            9999-9999-9999
            AA
            S
            individual
        
        9999999999
        
            99 ABC St
            home
            AAA
            AA
            ABC MMM
            AA
            Address
        
        1
        person

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 .

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?

Yitzhak Khabinsky · Accepted Answer

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'

    
        
            999-01-02T12:00:00-05:00
            ABC
            F
            ABC
            9999-9999-9999
            AA
            S
            individual
        
        9999999999
        
            99 ABC St
            home
            AAA
            AA
            ABC MMM
            AA
            Address
        
        1
        person
    
');
-- 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      |
+----+-----------+----------+

Extracting a value from an XML column in SQL Server

Answers (2)

Related Questions