XML Parsing & T-SQL -- Part 3

Question

The xml return, in part is as follows:

<Order xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:d1p2="http://schemas.abccompany.com/oml/package/1.0" xmlns:d1p1="http://schemas.abccompany.com/oml/batch/1.0" xmlns="http://schemas.abccompany.com/oml/base/1.0" intendedUse="0" quoteBack="5062-JA$181-3282" d1p1:transactionId="00000000-0000-0000-0000-000000000000" d1p2:uri="asdfasd-afdadfs-adsasdf" d1p1:customerId="0" d1p1:userId="0" d1p1:enabled="false" d1p1:priority="Low" d1p1:frequency="0" d1p1:recordCount="0" d1p1:executionPeriod="Once">
  <Security xmlns="http://schemas.abccompany.com/oml/security/1.0">
    <RootCredentials username="ust_3dResults2" password="1234567" />
    <LocationID>abcd</LocationID>
    <AccountID>9876</AccountID>
    <CustomerUserReferenceID>ssmart</CustomerUserReferenceID>
  </Security>
</Order>

Using t-sql, how would I pull the "AccountID" for example? I've tried:

;WITH xmlnamespaces('http://www.w3.org/2001/XMLSchema' AS xsd, DEFAULT 'http://www.w3.org/2001/XMLSchema')
SELECT adr.id, 
adr.omlinput,
adr.omlinput.value('(/Order/Security/AccountID)[1]', 'varchar(50)') AS [Results]
FROM [Reporting].[ApplicantDirectRequest] adr WITH (NOLOCK)
WHERE adr.OmlInput IS NOT NULL

and

;WITH xmlnamespaces('http://www.w3.org/2001/XMLSchema' AS xsd, DEFAULT 'http://www.w3.org/2001/XMLSchema')
SELECT adr.id, 
adr.omlinput,
adr.omlinput.value('(/xsd:Order/Security/AccountID)[1]', 'varchar(50)') AS [Results]
FROM [Reporting].[ApplicantDirectRequest] adr WITH (NOLOCK)
WHERE adr.OmlInput IS NOT NULL

marc_s · Accepted Answer · 2013-11-07 20:38:42Z

3

Try this:

;WITH xmlnamespaces('http://schemas.abccompany.com/oml/security/1.0' AS ns, 
                    'http://schemas.abccompany.com/oml/base/1.0' AS base)
SELECT 
    adr.id, 
    adr.omlinput.value('(/base:Order/ns:Security/ns:AccountID)[1]', 'int') AS [Results]
FROM 
    [Reporting].[ApplicantDirectRequest] adr
WHERE 
    ID = 1

The tricky part is: the <Order> element has the base namespace, while everything below it (the <Security> and <AccountID> elements) have the ns namespace.

Since no namespace extends over the whole XML fragment, you cannot really use a default namespace....

answered Nov 7, 2013 at 20:38

marc_s

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

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

4 Comments

John Waclawski Over a year ago

That did it, Marc! If I may trouble you for an explanation of how you came up with this solution that would be a bonus for me.

marc_s Over a year ago

@JohnWaclawski: you gotta carefully look at the namespaces that are defined. Those without prefix will be automatically applied to the XML element they're defined on (and anything below them, unless overridden). Then check all namespaces with prefixes and see where they are applied to XML elements. Those that aren't used anywhere (like those with the xsi and xsd prefix in your case) can be left out - the other need to be defined

John Waclawski Over a year ago

If I have a element within my xml...example: <Subject key="" package-uri="" role="Primary"> how would that be incorporated into my t-sql? Let's say it's <Subjects> <Subject key="" package-uri="" role="Primary"> <FirstName>Ron</FirstName> <LastName>Burgandy</LastName> </Subject> </Subjects>

marc_s Over a year ago

@JohnWaclawski: if an XML element doesn't define a default XML namespace (xmlns="..." without any prefix) and doesn't have a prefix on it, then it's part of the XML namespace that applies to its parent node (which might not be defined on the parent node directly, but further on up in the hierarchy)

Collectives™ on Stack Overflow

XML Parsing & T-SQL -- Part 3

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related