XQuery

So I recently had the opportunity to get back and write some XQueries for some reporting that I needed to do. I had forgotten some of the intricacies and had to dig way back in the noggin to recall them. Thought this time I would document them:

We store reporting data received back from third party feeds in XML format, in a string field. For example, a status back from eBay may look like the following


<apiresultofarrayofsynchinventoryitemresponse xsi="http://www.w3.org/2001/XMLSchema-instance" xsd="http://www.w3.org/2001/XMLSchema">
<status xmlns="http://api.channeladvisor.com/webservices/">Success</status>
<messagecode xmlns="http://api.channeladvisor.com/webservices/">0</messagecode>
<resultdata xmlns="http://api.channeladvisor.com/webservices/">
<synchinventoryitemresponse>
<sku>xxxxxx</sku>
<result>true</result>
</synchinventoryitemresponse>
</resultdata>
</apiresultofarrayofsynchinventoryitemresponse>



Problem was to retrieve all the SKUs from any of the returned reports like this only and just the SKU's for a list that could be exported into something like Excel. Here is where my problem fell. I forgot how to get around the namespaces above such as "xmns="http://api.channeladvisor.com/webservices/".

Well after doing some thinking, I remembered and here is the key "*:" this tell the XQuery to look at all namespaces in the query. The second part which came to me much quicker was the "only SKU" part. I had forgotten how to get just the data. This was much easier to figure out (I did go to the internet for this one) and very quickly was able to write a working query that looked as such


SELECT ProcessingReport.query('/APIResultOfArrayOfSynchInventoryItemResponse/*:ResultData/*:SynchInventoryItemResponse/*:Sku') AS SkuList
FROM ProcessingReport
WHERE ((AddedDate > '2009-08-29 23:59:00' AND ReportId < 669481) AND StatusCode = 'Success')