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">
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
WHERE ((AddedDate > '2009-08-29 23:59:00' AND ReportId < 669481) AND StatusCode = 'Success')