CODEWITHSUNDEEP
sql-serverxmlsql-server-2008
Chhatrapati Sharma
Chhatrapati Sharma

Reputation: 623

How to select xmlnode and value in sql server 2008

I have below xml in table

<GetOrdersResponse xmlns="urn:ebay:apis:eBLBaseComponents">
  <Timestamp>2013-09-07T06:38:46.192Z</Timestamp>
  <Ack>Success</Ack>
  <Version>837</Version>
  <Build>E837_CORE_APIXO_16317239_R1</Build>
  <HasMoreOrders>true</HasMoreOrders>
  <OrdersPerPage>100</OrdersPerPage>
  <PageNumber>1</PageNumber>
  <ReturnedOrderCountActual>48</ReturnedOrderCountActual>
  <PaginationResult>
    <TotalNumberOfPages>9</TotalNumberOfPages>
    <TotalNumberOfEntries>881</TotalNumberOfEntries>
  </PaginationResult>
  <OrderArray>
    <Order>
      <OrderID>1</OrderID>
    </Order>
    <Order>
      <OrderID>2</OrderID>
    </Order>
  </OrderArray>
</GetOrdersResponse>

I have written below query and getting 2 rows with balank(not null) results

select xmlresponse.value('(/GetOrdersResponse/OrderArray)[1]','varchar(max)')
from ordersxml

I want to result like below

Row1:   <Order>
          <OrderID>1</OrderID>
        </Order>

Row2:   <Order>
           <OrderID>2</OrderID>
        </Order>

Upvotes: 1

Views: 92

Answers (1)

marc_s
marc_s

Reputation: 754258

You're ignoring the XML namespace that is defined in your XML document!

<GetOrdersResponse xmlns="urn:ebay:apis:eBLBaseComponents">
                   ***************************************

You need to use that in your query - something like this:

;WITH XMLNAMESPACES(DEFAULT 'urn:ebay:apis:eBLBaseComponents')
    SELECT
        XOrder.query('.')
    FROM 
        dbo.YourTableInQuestionHere
    CROSS APPLY 
        XMLDATA.nodes('/GetOrdersResponse/OrderArray/Order') AS XTbl(XOrder)

This will return the two <Order> nodes and their contents back to you.

Upvotes: 1

Related Questions