November 19, 2009 at 2:26 pm
I have a table with a field named "Comment". I archive changes to this table using the xml datatype and now I'm trying to create an HTML email of the changes. I've got all my code working except that it won't read the xml element /Comment because comment() is a function in xpath to read actual comments in an xml document. I get the sense that I'm just going to have to change the name of the field in the table but that's a major undertaking. I was hoping someone might know of a way to read a node name /Comment.
Here's my code:
SET @tableHTML = N'<table border="1">' +
N'<tr><th>Key</th><th>TO Type</th><th>Employee</th><th>TO Hour(s)</th>' +
N'<th>Create Date</th><th>Create By</th><th>Comment</th><th>Earned</th></tr>' +
CAST(( SELECT SnapshotBefore.query(
'for $b in
/DocumentElement/Accrual
return (<tr><td>{$b/pkID}</td>
<td>{$b/TOType}</td>
<td>{$b/EmployeeID}</td>
<td>{$b/TOHours}</td>
<td>{$b/CreateDate}</td>
<td>{$b/CreateBy}</td>
<td>{$b/Comment}</td>
<td>{$b/IsEarned}</td>
</tr>)')
FROM
WHERE [changeID] = @changeID) AS NVARCHAR(MAX)) +
N'</table>'
November 19, 2009 at 3:11 pm
The name isn't your issue. This works:
declare @f xml
set @f='
<rt bob="">
<matt fun="1" notfun="2"><joe>uu</joe><comment> abcdefgh</comment></matt>
<matt fun="3" notfun="6"><comment> 987654asfd</comment></matt>
<matt fun="4" notfun="7"><comment> oooooooooooooooo</comment></matt>
</rt>'
select @f
select @f.query('for $b in /rt/matt
return
<Location>
<LocationID> { data($b/@fun) } </LocationID>
<h>{ data($b/u) }</h>
<td>{data($b/@notfun)}</td>
<td>{data(($b/comment)[1])}</td></Location>')
Nothing in your code jumps out as wrong, which in my case usually means I've either screwed up the pathing or the case of the node I want. Having some sample of the XML would help troubleshoot the issue.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 19, 2009 at 3:31 pm
Below is snip of my xml. As you can see, nothing fancy and all the other nodes output data as expected.
<DocumentElement>
<Accrual>
<pkID>0</pkID>
<TOType>VAC</TOType>
<EmployeeID>670099</EmployeeID>
<TOHours>80</TOHours>
<CreateDate>2009-11-19T13:49:48.0075745-05:00</CreateDate>
<CreateBy>6780112</CreateBy>
<Comment>Moved to eturnip 5/27/09</Comment>
<IsEarned>true</IsEarned>
</Accrual>
</DocumentElement>
November 19, 2009 at 3:40 pm
Here's the fixed code:
SET @tableHTML = N'<table border="1">' +
N'<tr><th>Key</th><th>TO Type</th><th>Employee</th><th>TO Hour(s)</th>' +
N'<th>Create Date</th><th>Create By</th><th>Comment</th><th>Earned</th></tr>' +
CAST(( SELECT SnapshotBefore.query(
'for $b in
/DocumentElement/Accrual
return (<tr><td>{data($b/pkID)}</td>
<td>{data($b/TOType)}</td>
<td>{data($b/EmployeeID)}</td>
<td>{data($b/TOHours)}</td>
<td>{data($b/CreateDate)}</td>
<td>{data($b/CreateBy)}</td>
<td>{data($b/Comment)}</td>
<td>{data($b/IsEarned)}</td>
</tr>)')
FROM --------------------------------------------------------------------------------.[dbo].[hrChangeLogXML]
WHERE [changeID] = @changeID) AS NVARCHAR(MAX)) +
N'</table>'
Thanks for the help Matt. After looking at your code in more detail I found my issue.
November 19, 2009 at 8:18 pm
nice - glad I could help!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply