none
Using T-SQL REPLACE (or XML) to Add an Attribute to Every Other <td> Tag

    Question

  • I have the following as part of a stored procedure that generates an HTML report based on the data inserted into the table variable "@tblTemp":

     

    SET @html =

    N'<table border="1" width="0" style="font-family: Verdana, sans-serif;">' +

    N'<tr><th>Name></th><th>ID</th></tr>' +

    N'<col align="left"></col><col align="right"></col>' +

    CAST ( ( SELECT td = [Name], '',

    td = IDNumber

    FROM @tblTemp

    ORDER BY ID

    FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;

    SET @html = @html + N'<br /><br /><img src="muuscreenshot.jpg" width="452" height="507" alt="sample.aspx" />'

     

    This generates a 2 column table that later in the stored procedure is sent out via sp_send_dbmail.  The problem I'm having is that I want the right (2nd) column of the table to be right-aligned.  For some reason, the line "

    N'<col align="left"></col><col align="right"></col>' +" doesn't right-align the 2nd column when the report is sent through email (althought it does work if I take the generated string a use it to manually create an HTML file).  So I want to add something like:

     

    SET @html = REPLACE(@html, '<td', '<td align="right"')

     

    but I still want the left column to be left-aligned, and this right-aligns everything.  Is it possible for me to use REPLACE (or some other string function) to replace every other (alternating) "<td", or should I be looking at parsing this string using some 2005 XML features (unfortunately, using a CLR function to do this is not an option, since I am not allowed to turn that on on the server I'm using)?  Thanks in advance.

     

    -Dave

    Thursday, January 10, 2008 11:31 PM

Answers

  • Something like this?

     

    Code Block

    DECLARE @x XML

    SET @x = '

    <table border="1" width="0" style="font-family: Verdana, sans-serif;">

    <tr>

    <th>Name</th>

    <th>IDNumber</th>

    </tr>

    <col align="left" />

    <col align="right" />

    <tr>

    <td>Item1</td>

    <td>1</td>

    </tr>

    <tr>

    <td>ItemTwo</td>

    <td>2</td>

    </tr>

    <tr>

    <td>Item3</td>

    <td>3</td>

    </tr>

    <tr>

    <td>ItemFour</td>

    <td>4</td>

    </tr>

    <tr>

    <td>Item5</td>

    <td>5</td>

    </tr>

    </table>'

     

     

    SELECT @x.query('

    <table border="1" width="0" style="font-family: Verdana, sans-serif;">

    <tr>

    <th>Name</th>

    <th>IDNumber</th>

    </tr>

    <col align="left" />

    <col align="right" />

    {

    for $node in /table/tr[node() >= 1]

    return

    <tr>

    <td>{data($node/td[1]/.)}</td>

    <td align="right">{data($node/td[2]/.)}</td>

    </tr>

    }

    </table>

    ') as a

     

    /*

    output:

     

    <table border="1" width="0" style="font-family: Verdana, sans-serif;">

    <tr>

    <th>Name</th>

    <th>IDNumber</th>

    </tr>

    <col align="left" />

    <col align="right" />

    <tr>

    <td>Item1</td>

    <td align="right">1</td>

    </tr>

    <tr>

    <td>ItemTwo</td>

    <td align="right">2</td>

    </tr>

    <tr>

    <td>Item3</td>

    <td align="right">3</td>

    </tr>

    <tr>

    <td>ItemFour</td>

    <td align="right">4</td>

    </tr>

    <tr>

    <td>Item5</td>

    <td align="right">5</td>

    </tr>

    </table>

    */

     

     

    Friday, January 11, 2008 9:42 AM
    Moderator
  • This is an XQuery expression. Here is what it try to do

     

    The line 'for $node in...' runs a loop for each <TR> in the XML.

    it takes each node and generates the xml for the result.

     

    each <tr> has two elements. The first value is needed without any change. so we print it as <td>{....}</d>. This will generate exactly the same content as what we have in the xml. But for the next node needs changes. We are inserting the attribute "align" in the second element.

     

    You can find some references in this white paper: http://technet.microsoft.com/en-us/library/ms345122.aspx

     

    There is a collection of XML whitepapers at http://www.sqlwhitepapers.com/2007/12/sql-server-2005-xml-white-papers.html

    Friday, January 11, 2008 7:16 PM
    Moderator

All replies

  • Could you post a sample XML?

     

    Friday, January 11, 2008 4:50 AM
    Moderator
  • Although I've changed the data in the table cells, this is basically the string that is output by the above query:

     

    <table border="1" width="0" style="font-family: Verdana, sans-serif;">
    <tr><th>Name</th><th>IDNumber</th></tr>
    <col align="left"></col><col align="right"></col>
    <tr><td>Item1</td><td>1</td></tr>
    <tr><td>ItemTwo</td><td>2</td></tr>
    <tr><td>Item3</td><td>3</td></tr>
    <tr><td>ItemFour</td><td>4</td></tr>
    <tr><td>Item5</td><td>5</td></tr>
    </table>

     

    I want to somehow (using a string function or converting this string to xml and then parsing it) add the align="right" attribute to the 2nd column (the td tags that contain the numeric values).

     

    Thanks,

    -Dave

     

    Friday, January 11, 2008 7:06 AM
  • Something like this?

     

    Code Block

    DECLARE @x XML

    SET @x = '

    <table border="1" width="0" style="font-family: Verdana, sans-serif;">

    <tr>

    <th>Name</th>

    <th>IDNumber</th>

    </tr>

    <col align="left" />

    <col align="right" />

    <tr>

    <td>Item1</td>

    <td>1</td>

    </tr>

    <tr>

    <td>ItemTwo</td>

    <td>2</td>

    </tr>

    <tr>

    <td>Item3</td>

    <td>3</td>

    </tr>

    <tr>

    <td>ItemFour</td>

    <td>4</td>

    </tr>

    <tr>

    <td>Item5</td>

    <td>5</td>

    </tr>

    </table>'

     

     

    SELECT @x.query('

    <table border="1" width="0" style="font-family: Verdana, sans-serif;">

    <tr>

    <th>Name</th>

    <th>IDNumber</th>

    </tr>

    <col align="left" />

    <col align="right" />

    {

    for $node in /table/tr[node() >= 1]

    return

    <tr>

    <td>{data($node/td[1]/.)}</td>

    <td align="right">{data($node/td[2]/.)}</td>

    </tr>

    }

    </table>

    ') as a

     

    /*

    output:

     

    <table border="1" width="0" style="font-family: Verdana, sans-serif;">

    <tr>

    <th>Name</th>

    <th>IDNumber</th>

    </tr>

    <col align="left" />

    <col align="right" />

    <tr>

    <td>Item1</td>

    <td align="right">1</td>

    </tr>

    <tr>

    <td>ItemTwo</td>

    <td align="right">2</td>

    </tr>

    <tr>

    <td>Item3</td>

    <td align="right">3</td>

    </tr>

    <tr>

    <td>ItemFour</td>

    <td align="right">4</td>

    </tr>

    <tr>

    <td>Item5</td>

    <td align="right">5</td>

    </tr>

    </table>

    */

     

     

    Friday, January 11, 2008 9:42 AM
    Moderator
  • Thanks, this is great!  I've only used the xml query a minimal amount and so I don't completely understand what the following code is doing:

     

    {

    for $node in /table/tr[node() >= 1]

    return

    <tr>

    <td>{data($node/td[1]/.)}</td>

    <td align="right">{data($node/td[2]/.)}</td>

    </tr>

    }

     

    Could you point me to any reference material that will explain this for my own learning/knowledge?  Either way, thanks again, your code works great!

     

    Thanks,

    -Dave

     

    Friday, January 11, 2008 6:50 PM
  • This is an XQuery expression. Here is what it try to do

     

    The line 'for $node in...' runs a loop for each <TR> in the XML.

    it takes each node and generates the xml for the result.

     

    each <tr> has two elements. The first value is needed without any change. so we print it as <td>{....}</d>. This will generate exactly the same content as what we have in the xml. But for the next node needs changes. We are inserting the attribute "align" in the second element.

     

    You can find some references in this white paper: http://technet.microsoft.com/en-us/library/ms345122.aspx

     

    There is a collection of XML whitepapers at http://www.sqlwhitepapers.com/2007/12/sql-server-2005-xml-white-papers.html

    Friday, January 11, 2008 7:16 PM
    Moderator