XQuery Lab 47 – Generating HTML table from XML Data

Getting started with SSIS - Part 1: Introduction to SSIS

First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

Jacob's Blog

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.

Connect your existing Source Control system to SSMS in 5 minutes

Recent Blog Posts

XQuery Lab 47 – Generating HTML table from XML Data

Welcome to XQuery Lab 47. In this lab, we will see how to generate an HTML table from an XML document, using XQuery.

Here is the source data:

<CUST COMPANY="Company1" CONTACT="Jacob" />
<CUST COMPANY="Company1" CONTACT="Michael" />
<CUST COMPANY="Company3" CONTACT="Steve" />

Here is the output required

<table>
  <tr>
    <td>Company</td>
    <td>Contact</td>
  </tr>
  <tr>
    <td>Company1</td>
    <td>Jacob</td>
  </tr>
  <tr>
    <td>Company1</td>
    <td>Michael</td>
  </tr>
  <tr>
    <td>Company3</td>
    <td>Steve</td>
  </tr>
</table>

Here is the TSQL code using XQuery to generate the required output.

DECLARE @x XML
SELECT @x = '
<CUST COMPANY="Company1" CONTACT="Jacob" />
<CUST COMPANY="Company1" CONTACT="Michael" />
<CUST COMPANY="Company3" CONTACT="Steve" />'

SELECT
    @x.query('
        <table>
            <tr>
                <td>Company</td>
                <td>Contact</td>
            </tr>
            {
                for $r in CUST
                return
                <tr>
                    <td>{data($r/@COMPANY)}</td>
                    <td>{data($r/@CONTACT)}</td>
                </tr>
            }
        </table>
')

/*
<table>
  <tr>
    <td>Company</td>
    <td>Contact</td>
  </tr>
  <tr>
    <td>Company1</td>
    <td>Jacob</td>
  </tr>
  <tr>
    <td>Company1</td>
    <td>Michael</td>
  </tr>
  <tr>
    <td>Company3</td>
    <td>Steve</td>
  </tr>
</table>
*/