need to shred xml column

[andreadd]

sorry - didnt know how else to name this topic.

environment: SQL2008 standard SP1 / Goldmine Premium 9 / Crystal Advantage

My current task is to get reporting and dashboards going for forecasted sales. GoldMine has put their notes into an XML format. We are trying to use a sales forecasting tool that allows for multiple products to be rolled up under a single sale however they are listed in notes.

I currently have a SQL view going to get the details of the sale

SELECT USERID, ACCOUNTNO, ONDATE, ONTIME, ACTVCODE, RECTYPE, DURATION, NUMBER1, NUMBER2, COMPANY, REF, CREATEON, CREATEBY,
LASTUSER, LASTDATE, CAST(CAST(NOTES AS varbinary(MAX)) AS varchar(MAX)) AS NOTEZ, LOPRECID
FROM dbo.CAL
WHERE (CREATEON >= '7/1/2010') AND (RECTYPE = 'S') AND (ACTVCODE = '$$$')

the column called NOTEZ is the xml data.

it looks like this:
<div></div>
<div id="1"></div>
<tr id="1" bgcolor="#e0e9ff">
<td id="Product" val="Equipment">Equipment</td>
<td id="Code" val=""> </td>
<td id="Units" val="0">0</td>
<td id="Price" align="right" val="0">$0.00</td>
<td id="Amount" align="right" val="0">$0.00</td>
</tr>
<div></div>
<div id="2"></div>
<tr id="2" bgcolor="#f7f5c8">
<td id="Product" val="Securitas Vision(SV)">Securitas Vision(SV)</td>
<td id="Code" val=""> </td>
<td id="Units" val="0">0</td>
<td id="Price" align="right" val="0">$0.00</td>
<td id="Amount" align="right" val="0">$0.00</td>
</tr>

what I am trying to do is to take the view a step further, list all of the stuff defined (accountno, ondate, etc) but also seperate the xml. I know I am looking at a lotta extra rows but as long as the accountno and loprecid are present in each row I have my "glue' to keep it together in reporting.

Any suggestions about how to do this?

 

[markros]

I suggest to take a look at tnis blog post
Referencing Parent nodes in XQuery is expensive that explaines possible solutions to the problem.

PluralSight Learning Library