Extraction d'une valeur d'une colonne XML dans SQL Server

Question

J'essaie d'extraire les valeurs de données présentes dans le fichier FirstName y LastName à partir du XML ci-dessous qui est présent sous forme de chaîne dans une colonne d'une table SQL Server.

<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>

Voici la requête que j'ai essayée :

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

Cette requête renvoie les données dans tous les sous-nœuds du nœud XML <AccountHolderContact> .

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

Lorsque je modifie ma requête comme suit, je n'obtiens aucune donnée dans ma colonne de sortie. FirstName :

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

Y a-t-il une raison pour laquelle je ne peux pas extraire des sous-nœuds de AccountHolderContact ? Si non, quel est le moyen le plus simple de le faire ?

Answer 1

Votre XML a plusieurs espaces de noms - 17 au total. Seuls deux d'entre eux doivent être pris en compte. Il est préférable de ne pas utiliser les caractères génériques des espaces de noms pour des raisons de performances.

Voici comment déchiqueter votre XML et récupérer ce dont vous avez besoin.

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);

Sortie

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

Answer 2

En supposant que le XML ne peut contenir que 1 AccountHolderContact (ou similaire), vous pouvez alors utiliser l'entité WITHXMLSPACES et l'opérateur XML value pour obtenir l'information :

DECLARE @XML xml = '<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>';

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 ns6)
SELECT V.X.value('(ns6:Account/ns6:AccountHolderContact/entity-Person/FirstName/text())[1]','nvarchar(20)') AS FirstName,
       V.X.value('(ns6:Account/ns6:AccountHolderContact/entity-Person/LastName/text())[1]','nvarchar(20)') AS FirstName
FROM(VALUES(@XML))V(X);

Si, toutefois, vous avez plus d'une personne dans les données, alors vous pouvez utiliser nodes pour obtenir une ligne par personne (cela ramènera toujours une ligne avec les mêmes données d'échantillon). Si AccountHolderContact est l'élément répétitif, cela ressemblera à ceci :

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 ns6)
SELECT A.AHC.value('(entity-Person/FirstName/text())[1]','nvarchar(20)') AS FirstName,
       A.AHC.value('(entity-Person/LastName/text())[1]','nvarchar(20)') AS FirstName
FROM(VALUES(@XML))V(X)
    CROSS APPLY V.X.nodes('ns6:Account/ns6:AccountHolderContact')A(AHC);