T-SQL cannot query XML with multiple nested namespaces and no unique ID

Question

I need to create a query from an xml file that hcontains 4 namespaces but none of them have a unique id. eg. xmlns:fb

I have listed them below:

xmlns="http://xxx/pie/svc/frs/FindRegistration/2.0.0"
xmlns="http://xxx/pie/xsd/frs/FindRegistrationMessages/2.0.0"
xmlns="http://xxx/pie/xsd/common/CommonCoreElements/2.0.0"
xmlns="http://xxx/pie/xsd/frs/PractitionerRegistrationElements/2.0.0"

If I strip all the namespaces (through Visual Studio) from the document I can retrieve the data fine, but I need to keep the namespaces intact.

<FindRegistrationsResponse xmlns="http://xxx/pie/svc/frs/FindRegistration/2.0.0">
    <AuditDetails xmlns="http://xxx/pie/xsd/frs/FindRegistrationMessages/2.0.0">
        <QueryIdentifier xmlns="http://xxx/pie/xsd/common/CommonCoreElements/2.0.0">8901823</QueryIdentifier>
        <PractitionerCount xmlns="http://xxx/pie/xsd/common/CommonCoreElements/2.0.0">134140</PractitionerCount>
        <ServiceMessagesCount xmlns="http://xxx/pie/xsd/common/CommonCoreElements/2.0.0">2</ServiceMessagesCount>
        <TotalRecordsSearched xmlns="http://xxx/pie/xsd/common/CommonCoreElements/2.0.0">134142</TotalRecordsSearched>
        <TotalNumberOfRegistration xmlns="http://xxx/pie/xsd/common/CommonCoreElements/2.0.0">257034</TotalNumberOfRegistration>
        <SnapshotDate xmlns="http://xxx/pie/xsd/common/CommonCoreElements/2.0.0">2018-06-29</SnapshotDate>
    </AuditDetails>
    <ProfessionNumberReplay ProfessionNumber="MED0000xxxxx" xmlns="http://xxx/pie/xsd/frs/FindRegistrationMessages/2.0.0">
        <Practitioner>
            <PractitionerIdentifier xmlns="http://xxx/pie/xsd/frs/PractitionerRegistrationElements/2.0.0">xxxxxxxx</PractitionerIdentifier>
            <PractitionerName NameEditDate="2010-07-03T14:14:35.377" xmlns="http://xxx/pie/xsd/frs/PractitionerRegistrationElements/2.0.0">
                <NameTitle>Dr</NameTitle>
                <FamilyName>Smith</FamilyName>
                <GivenName>John</GivenName>
                <MiddleName />
            </PractitionerName>
            <Demographics xmlns="http://xxx/pie/xsd/frs/PractitionerRegistrationElements/2.0.0">
                <Gender>GenderName</Gender>
            </Demographics>
            <Qualification QualificationEditDate="2016-08-10T19:35:54.067" xmlns="http://xxx/pie/xsd/frs/PractitionerRegistrationElements/2.0.0">
                <QualificationTitle>Bachelor of Medicine / Bachelor of Surgery </QualificationTitle>
                <AwardingInstitution>University of xxx</AwardingInstitution>
                <CountryQualificationObtained>Country Name</CountryQualificationObtained>
                <YearOfQualification>1999</YearOfQualification>
            </Qualification>
            <Address AddressEditDate="2010-07-03T14:15:48.607" xmlns="http://xxx/pie/xsd/frs/PractitionerRegistrationElements/2.0.0">
                <AustralianAddress>
                    <AustralianLocality>SUBURB NAME</AustralianLocality>
                    <AustralianPostcode>2000</AustralianPostcode>
                    <AustralianState>NSW</AustralianState>
                    <Country>Australia</Country>
                </AustralianAddress>
            </Address>
            <Profession xmlns="http://xxx/pie/xsd/frs/PractitionerRegistrationElements/2.0.0">
                <ProfessionNumber>MED0000xxxxx</ProfessionNumber>
                <Profession>Medical Practitioner</Profession>
                <ProfessionStartDate>1999-05-10T00:00:00</ProfessionStartDate>
                <Registration>
                    <RecordNumber>001</RecordNumber>
                    <RegistrationType>Limited (Public Interest - Occasional Practice)</RegistrationType>
                    <RegistrationStatus>Unregistered</RegistrationStatus>
                    <RegistrationSubStatus>Withdrawn</RegistrationSubStatus>
                    <RegistrationToDate>2013-09-30T00:00:00</RegistrationToDate>
                    <InitialRegistrationDate>1965-05-10T00:00:00</InitialRegistrationDate>
                </Registration>
                <Condition ConditionEditDate="2012-10-22T14:00:10.5">
                    <ConditionType>Registration</ConditionType>
                    <ConditionDetail>text here</ConditionDetail>
                </Condition>
            </Profession>
        </Practitioner>
    </ProfessionNumberReplay>
</FindRegistrationsResponse>

This is the T-SQL code that I've tried:

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XMLData FROM AHPRA_XML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML


;WITH XMLNAMESPACES('http://xxx/pie/svc/frs/FindRegistration/2.0.0',
                    'http://xxx/pie/xsd/frs/FindRegistrationMessages/2.0.0',
                    'http://xxx/pie/xsd/common/CommonCoreElements/2.0.0',
                    'http://xxx/pie/xsd/frs/PractitionerRegistrationElements/2.0.0')

SELECT DISTINCT  ProfessionNumber, NameTitle, GivenName, MiddleName, FamilyName, Gender

FROM OPENXML(@hDoc, 'FindRegistrationsResponse/ProfessionNumberReplay/Practitioner')
WITH 
(
ProfessionNumber [varchar](50) '../@ProfessionNumber',
NameTitle [varchar](20) 'PractitionerName/NameTitle',
GivenName  [varchar](100) 'PractitionerName/GivenName',
MiddleName  [varchar](100) 'PractitionerName/MiddleName',
FamilyName [varchar](100) 'PractitionerName/FamilyName',
Gender [varchar](10) 'Demographics/Gender'
)

EXEC sp_xml_removedocument @hDoc
GO

marc_s · Accepted Answer · 2019-03-22 05:29:37Z

4

You've defined the namespaces - but you never use them in your XPath! Also, I'd strongly recommend to use the built-in XQuery capabilities over the legacy OPENXML approach.

Try this select statement:

;WITH XMLNAMESPACES('http://xxx/pie/svc/frs/FindRegistration/2.0.0' AS root, 
                    'http://xxx/pie/xsd/frs/FindRegistrationMessages/2.0.0' AS msg,
                    'http://xxx/pie/xsd/frs/PractitionerRegistrationElements/2.0.0' AS el)
    SELECT
        ProfessionNumber = XC.value('(../@ProfessionNumber)', 'varchar(50)'),
        NameTitle = XC.value('(el:PractitionerName/el:NameTitle)[1]', 'varchar(50)'),
        GivenName = XC.value('(el:PractitionerName/el:GivenName)[1]', 'varchar(50)'),
        MiddleName = XC.value('(el:PractitionerName/el:MiddleName)[1]', 'varchar(50)'),
        FamilyName = XC.value('(el:PractitionerName/el:FamilyName)[1]', 'varchar(50)'),
        Gender = XC.value('(el:Demographics/el:Gender)[1]', 'varchar(50)')
    FROM
        @XML.nodes('/root:FindRegistrationsResponse/msg:ProfessionNumberReplay/msg:Practitioner') AS XT(XC)

This should grab the desired data from the XML string.

Update:

when you the XML you provided in the question, and this code here, you do get this output:

So either you're not really working with the sample XML you've provided, or you are using different code......

edited Mar 22, 2019 at 5:29

answered Feb 18, 2019 at 5:59

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Walkdog Over a year ago

Thanks for the select statement but it returns no data. I have provided a screenshot of the XML structure in this link - imgur.com/a/tJ0ApS7 Also "root" appears to be a reserved word in T-SQL but changing this does not return any data either..

marc_s Over a year ago

@Walkdog: the code I provided does work for the XML you've showed in your question. See my update. So my guess is your XML you're trying this code on is not the same structure (especially the XML namespaces) as your sample posted here

Walkdog Over a year ago

My apologies - you are right. I was attempting to run the statement with the XML of which I had removed the namespaces. The script does work perfectly on the original file. Many thanks.

Collectives™ on Stack Overflow

T-SQL cannot query XML with multiple nested namespaces and no unique ID

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related