CODEWITHSUNDEEP
sqlsql-serverxmlopenxml
Pinaki Mukherjee
Pinaki Mukherjee

Reputation: 1656

SQL Server open XML select values

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

Upvotes: 1

Views: 1395

Answers (1)

Xedni
Xedni

Reputation: 4695

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)

Upvotes: 2

Related Questions