How to get specified attributes from an xml column in Sqlserver

Question

I have a xml column called MesssageContent in my db as:

<ns1:ProductAvailabilityPublication xmlns:ns0="http://www.example.com/Ecommerce/Worldwide/AvailabilityService/Schemas/ProductResponse" xmlns:ns2="http://www.example.com/Ecommerce/Worldwide/AvailabilityService/Schemas/ProductRequest" xmlns:ns1="http://www.example.com/Ecommerce/Worldwide/AvailabilityService/Schemas/ProductAvailabilityPublish" RequestorID="WCS_AU" BatchID="36dae836-cc16-489c-8009-afab63f92dff">
  <ns0:ProductResponse ProductNumber="C8010A" LeadTime="1" LastUpdated="2014-07-18T11:17:47.677" Region="AP" CountryCode="AU" CurrInv="259" FutureInv="0" CurrEDD="TBA" FutureEDD="TBA" ETA="0" D-Flag="True">
    <ns0:ProductType>
      <ns2:NonStartingPoint />
    </ns0:ProductType>
    <ns0:Status>
      <ns0:Success />
    </ns0:Status>
  </ns0:ProductResponse>
  <ns0:ProductResponse ProductNumber="Q3655A" LeadTime="1" LastUpdated="2014-07-18T11:17:47.677" Region="AP" CountryCode="AU" CurrInv="2" FutureInv="0" CurrEDD="TBA" FutureEDD="TBA" ETA="0" D-Flag="True">
    <ns0:ProductType>
      <ns2:NonStartingPoint />
    </ns0:ProductType>
    <ns0:Status>
      <ns0:Success />
    </ns0:Status>
  </ns0:ProductResponse>
  <ns0:ProductResponse ProductNumber="PP654A" LeadTime="21" LastUpdated="2014-07-18T11:17:47.677" Region="AP" CountryCode="AU" CurrInv="0" FutureInv="0" CurrEDD="TBA" FutureEDD="TBA" ETA="0" D-Flag="False">
    <ns0:ProductType>
      <ns2:NonStartingPoint />
    </ns0:ProductType>
    <ns0:Status>
      <ns0:Success />
    </ns0:Status>
  </ns0:ProductResponse>

</ns1:ProductAvailabilityPublication>

Now I want to find out all "ProductNumber" in "CurrInv">0 and "D-flag"="True" records. I tried but only can find out the whole xml colomn which with not needed data included. Is it possible to figure out all "ProductNumber" to meet my expression?

How people supposed to know where ProductNumber, CurrInv, and D-flag you're talking about are located? Post sample XML and explain based on that sample to make your question understandable. — har07, Jul 23 '14 at 10:28
Sorry I don't know how to add line break in the code as I add code markdown but there isn't any line break at all. — Darren, Jul 23 '14 at 10:54
You would do better to just post the XML, indented 4 spaces using the `{}` tool. — John Saunders, Jul 23 '14 at 10:54

score 0 · Accepted Answer · answered Jul 23 '14 at 11:00

0

The following XQuery should work. You need to bind the correct namespaces (or use the namespace wildcard * instead of the specific namespaces):

//ns0:ProductResponse[@CurrInv > 0][@D-Flag = "True"]/@ProductNumber/string()

answered Jul 23 '14 at 11:00

dirkk

6,160
5
33
51

How to get specified attributes from an xml column in Sqlserver

1 Answers1