[xquery-talk] Shredding xml stored in an xml data type in SQL 2005 (Yukon)

[Catherine Borchardt]

I am attempting to find a more streamlined method of shredding an xml 
document stored in an xml data type in SQL 2K5. Previously, I was using 
the following code, which basically breaks out the elements one by one: 
SELECT 
cast(xmldocument.query(' 
   declare namespace AWMI="http://Navision.Promega.Com"; 
for $OrdNo in 
//AWMI:NavisionSalesCreditMemo_v370/AWMI:Body/AWMI:Headers/AWMI:Header/A
WMI:No 
      return 
        string($OrdNo) 
') as nvarchar (100)) as OrderNo,..., 
FROM salesdatamartstaging.dbo.navisiondocuments 
where documentname like 'navisionsalescreditmemo%' 
It works, but as you can imagine, it is cumbersome when it gets to a 
document that has a large number of elements. 
So I am working on this query: 
SELECT documentname, nref.value('No1[1]', 'varchar(100)') No, 
nref.value('SelltoCustNo[1]', 'varchar(100)') Customer, 
nref.value('ShiptoAddress[1]', 'varchar(100)') ShiptoAddress 
FROM navisiondocuments 
CROSS APPLY 
xmldocument.nodes(' 
declare namespace AWMI="http://Navision.Promega.Com"; 
declare namespace
g="http://Navision.Promega.Com";/AWMI:NavisionSalesCreditMemo_v370/AWMI:
Body/AWMI:Headers/AWMI:Header 
' 
) AS AWMI(nref)  WHERE documentname like '%SalesCredit%' 
No syntax errors, but I am getting NULL values for all my nref 
values...it seems not to be getting at the xml data properly. Anyone 
have ideas? 
I can send along the document this is being applied against if that 
helps... 
Cat