SQL Server open XML select values

Question

I am trying to select some values from order xml using openxml api. Whenever I am executing this command I am seeing Null results.The statement I am writing under select - with may be wrong. Any suggestion how can I get the correct value.

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

SELECT @XML = N'<order>
  <order-date>2017-07-24T20:48:57.000Z</order-date>
  <invoice-no>00000001</invoice-no>
  <customer>
    <customer-name>abc 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>abc</card-holder>
        <expiration-month>1</expiration-month>
        <expiration-year>2021</expiration-year>
      </credit-card>
      <amount>325.48</amount>
    </payment>
  </payments>
</order>';


EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML


SELECT *
FROM OPENXML(@hDoc, '/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

Xedni · Accepted Answer · 2018-03-28 00:14:11Z

2

You're referencing those fields as though they were attributes (with the @ sign) but they're actually elements. Try removing the @ sign. e.g.

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

select hDoc = @hDoc
SELECT *
FROM OPENXML(@hDoc, '/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

Also, this is just a personal preference thing, but unless you're XML documents are really large, you might look into using @xml.nodes() instead. It doesn't have to load the full document into memory first, and also doesn't run the risk of accidentally not unloading the document handle. You could equivalently write the first query like this:

SELECT 
    t.c.value('customer-name[1]', 'varchar(50)'),
    t.c.value('customer-email[1]', 'varchar(50)')
FROM @xml.nodes('/order/customer') t(c)

edited Mar 28, 2018 at 0:14

answered Mar 28, 2018 at 0:09

Xedni

4,7913 gold badges22 silver badges38 bronze badges

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

1 Comment

Pinaki Mukherjee Over a year ago

Thanks, it worked. I will accept your answer as correct one. I need to read the documentation

Collectives™ on Stack Overflow

SQL Server open XML select values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related