SQL Server knowledge center

Relational to XML transformation with XQuery

By : Kasim Wirama, MCDBA, MCITP

XQuery could not only do XML manipulation from XML data but also it can convert relational (traditional column) to XML result. 2 XQuery functions implements the purpose, i.e. sql:column and sql:variable.

I use table Person.Person at AdventureWorks2008 to demonstrate transformation from traditional column to XML result and Production.Product to demonstrate combination between sql:column and sql:variable.

Person.person table contains information about primary key (BusinessEntityID column) and person’s first name, middle name and last name. you are asked to output them in the following XML format here:

<person>
                <id> {businessEntityID} </id>
                <fullname>
                                {firstname middlename lastname}
                </fullname>
</person>

With sql:column function, you can issue this query statement :

DECLARE @x XML;
SET @x = '';

SELECT
@x.query('<person>
                <id>{sql:column("BusinessEntityID")}</id>
                <fullname>{
                                                sql:column("FirstName"),
                                                sql:column("MiddleName"),
                                                sql:column("Lastname")
                                                }
                </fullname>
</person>')
FROM Person.Person AS p

You will can figure out what the result will be after executing the sample query above.

Now, you are asked to display product information (product name, product number, product’s price and product’s discounted price after discount) from Production.product table with 20% discount. Because discount 20% comes from variable, you need to specify sql:variable in XML query. If you are asked to display required output here :

<Product>
  <productname> {product name} </productname>
  <productnumber> {product number} </productnumber>
  <price>
    <fullprice> {list price} </fullprice>
    <discountprice> {list price after discount} </discountprice>
  </price>
</Product>

You can issue XQuery statement below with combination of sql:column and sql:variable functions.

DECLARE @x XML, @discount NUMERIC(3,2);
SELECT @x = '', @discount = 0.02;

SELECT
@x.query('<Product>
<productname>
                {sql:column("name")}
</productname>
<productnumber>
                {sql:column("ProductNumber")}
</productnumber>
<price>
                <fullprice>
                                {sql:column("ListPrice")}
                </fullprice>
                <discountprice>
                                {sql:column("ListPrice") - sql:column("ListPrice") * sql:variable("@discount")}
                </discountprice>
</price>
</Product>')
FROM Production.Product
WHERE ListPrice > 0

Share this post: | | | |