Open XML select values with XML name space

Question

I am trying to select some values using open xml in sql server 2012. This works when I don't have any xml name space. But whenever the below prefix get added with root element, I am not able to select values. Any suggestion how I can select values with xmlns:

xmlns="somenamspace/2006-10-31" order-no="00000001"

USE grails
GO


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

SELECT @XML = N'<order xmlns="somenamspace/2006-10-31" order-no="00000001">
  <order-date>2017-07-24T20:48:57.000Z</order-date>
  <original-order-no>00000001</original-order-no>
    <customer>
    <customer-name>abcd abcd</customer-name>
    <customer-email>[email protected]</customer-email>
  </customer>
   <current-order-no>00000001</current-order-no>
  <payments>
    <payment>
      <credit-card>
        <card-type>VISA</card-type>
        <card-number>XXXX-XXXX-XXXX-1111</card-number>
        <card-holder>abcd</card-holder>
        <expiration-month>1</expiration-month>
        <expiration-year>2021</expiration-year>
      </credit-card>
      <amount>325.48</amount>
    </payment>
  </payments>
  </order>';

SET @rootxmlns = '<root xmlns:ns1="somenamspace/2006-10-31"/>'
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML, @rootxmlns


SELECT orderNo
FROM OPENXML(@hDoc, 'ns1:order',2)
WITH 
(
orderNo [varchar](50) 'original-order-no'
)


SELECT *
FROM OPENXML(@hDoc, 'ns1:order/customer',2)
WITH 
(
customerName [varchar](50) 'customer-name',
customerEmail [varchar](100) 'customer-email'
)


SELECT cardType, cardNumber, cardHolder
FROM OPENXML(@hDoc, '/order/payments/payment/credit-card',2)
WITH 
(
cardType [varchar](50) 'card-type',
cardNumber [varchar](100) 'card-number',
cardHolder [varchar](100) 'card-holder'
)

EXEC sp_xml_removedocument @hDoc
GO

Gottfried Lesigang · Accepted Answer · 2018-03-28 07:28:50Z

1

Great, that you've found an answer yourself, but this can be solved better.

FROM OPENXML with the corresponding SPs to open and to remove a document is outdated and should not be used any more. Rather use the methods, the native XML type provides:

The following will give you at least some templates how to access the values within your XML:

DECLARE @XML AS XML=
N'<order xmlns="somenamspace/2006-10-31" order-no="00000001">
  <order-date>2017-07-24T20:48:57.000Z</order-date>
  <original-order-no>00000001</original-order-no>
  <customer>
    <customer-name>abcd abcd</customer-name>
    <customer-email>[email protected]</customer-email>
  </customer>
  <current-order-no>00000001</current-order-no>
  <payments>
    <payment>
      <credit-card>
        <card-type>VISA</card-type>
        <card-number>XXXX-XXXX-XXXX-1111</card-number>
        <card-holder>abcd</card-holder>
        <expiration-month>1</expiration-month>
        <expiration-year>2021</expiration-year>
      </credit-card>
      <amount>325.48</amount>
    </payment>
  </payments>
</order>';

--The query

WITH XMLNAMESPACES(DEFAULT N'somenamspace/2006-10-31')
SELECT @xml.value(N'(/order/@order-no)[1]',N'int') AS OrderNumber
      ,@xml.value(N'(/order/order-date/text())[1]',N'datetime') AS OrderDate
      ,@xml.value(N'(/order/customer/customer-name/text())[1]',N'nvarchar(max)') AS CustomerName
      ,p.value(N'local-name(.)',N'nvarchar(max)') AS PaymentType
      ,p.value(N'(card-type/text())[1]','nvarchar(max)') AS CardType
      ,p.value(N'(../amount/text())[1]','decimal(10,4)') AS Amount
FROM @xml.nodes(N'/order/payments/payment/*[local-name()!="amount"]') AS A(p)

The result

Nr OrderDate                CustomerName    PaymentType   CardType  Amount
1  2017-07-24 20:48:57.000  abcd abcd       credit-card   VISA      325.4800

Explanation

Some data is taken directly via XPath out of @xml. The statement FROM @xml.nodes() will create a derived table of <payments><payment> nodes (as the wording suggests a 1:n relationship. The <amount> node is handled explicitly, the other node within <payment> is taken as payment details.

answered Mar 28, 2018 at 7:28

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

3 Comments

Pinaki Mukherjee Over a year ago

Thank you so much for showing the right way. I have one more question if I have multiple credit cards how can I loop them over , instead of passing number directly - @xml.value(N'(/order/@order-no)[1]',N'int')

Gottfried Lesigang Over a year ago

@PinakiMukherjee Please start a new question with some realistic (but reduced) example of your XML and state the expected output. New questions raise the highest attraction. I (or somebody else) will come by soon...

Pinaki Mukherjee Over a year ago

Thanks Shungo. I will open a new one.

Pinaki Mukherjee · Accepted Answer · 2018-03-28 01:27:55Z

Got it,need to add name space in property level as well:

SET @rootxmlns = '<root xmlns:ns1="http://www.demandware.com/xml/impex/order/2006-10-31"/>'
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML, @rootxmlns


SELECT orderNo
FROM OPENXML(@hDoc, 'ns1:order',2)
WITH 
(
orderNo [varchar](50) 'ns1:original-order-no'
)


SELECT *
FROM OPENXML(@hDoc, 'ns1:order/ns1:customer',2)
WITH 
(
customerName [varchar](50) 'ns1:customer-name',
customerEmail [varchar](100) 'ns1:customer-email'
)


SELECT cardType, cardNumber, cardHolder
FROM OPENXML(@hDoc, 'ns1:order/ns1:payments/ns1:payment/ns1:credit-card',2)
WITH 
(
cardType [varchar](50) 'ns1:card-type',
cardNumber [varchar](100) 'ns1:card-number',
cardHolder [varchar](100) 'ns1:card-holder'
)

EXEC sp_xml_removedocument @hDoc
GO

Collectives™ on Stack Overflow

Open XML select values with XML name space

2 Answers 2

Explanation

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Explanation

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related