SLQXML Template
SLQXML Template
In addition to taking advantage of Microsoft SQLXML v3.0 through the SQLXML Adapter FPC, in this solution we have created an IIS virtual directory to consume XML generated from a series of queries that represent our Budget Variance report. The SQLXML query is built as an XML document containing Transact-SQL queries which populate the different portions of the XML hierarchy.
The structure of a SQL XML Template looks something like the following:
<RootNode xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:header>
<sql:param name='param1'>default value</sql:param>
</sql:header>
<myNode>
<sql:query>
SELECT name FROM employees FOR XML RAW
</sql:query>
</myNode>
</RootNode>
Our SQLXML template involves many different queries against the Finance database. Each query is encapsulated in at least one layer of XML Nodes that we have defined in addition to any nodes that the Transact-SQL statements define. See the SQL XML Books OnLine for more information on how to return XML from Transact-SQL queries. In the following XML that describes the SQLXML template, the XML elements are bolded to set them off from the Transact-SQL queries.
<budgetVariance xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:header>
<sql:param name='reportyear'>1974</sql:param>
<sql:param name='budgetid' >-1</sql:param>
<sql:param name='reportmonth' >0</sql:param>
</sql:header>
<baselineInfo>
<projectInfo>
<sql:query>
SELECT
projecttitle,
CONVERT(VARCHAR(2),DATEPART(mm,start_date)) + '/' +
CONVERT(VARCHAR(2),DATEPART(dd,start_date)) + '/' +
CONVERT(VARCHAR(4),DATEPART(yy,start_date)) as 'startDate',
CONVERT(VARCHAR(2),DATEPART(mm,end_date)) + '/' +
CONVERT(VARCHAR(2),DATEPART(dd,end_date)) + '/' +
CONVERT(VARCHAR(4),DATEPART(yy,end_date)) AS 'endDate',
CASE
WHEN Summary IS null THEN 'N/A'
ELSE Summary
END AS 'summary',
CASE
WHEN budgetjustification IS null THEN 'N/A'
ELSE budgetjustification
END AS 'justification'
FROM
v_budget_info
WHERE
bgt_id = @budgetid
FOR XML raw
</sql:query> </projectInfo> <totalBudget> <sql:query>
SELECT
SUM(value) AS 'totalBudget'
FROM
v_budget_item_normalized
WHERE
bgt_itm_id in (
SELECT bgt_itm_id
FROM budget_item
WHERE bgt_id = @budgetid) AND
bgt_fy = @reportyear
FOR XML RAW
</sql:query> </totalBudget> <costToDate> <sql:query>
SELECT
SUM(exp_amount) AS 'costToDate'
FROM
actual_cost
WHERE
DATEPART(mm,exp_date) <= @reportmonth AND
DATEPART(yy,exp_date) = @reportyear AND
bgt_itm_id in (SELECT bgt_itm_id FROM budget_item WHERE bgt_id = @budgetid)
FOR XML RAW
</sql:query> </costToDate> </baselineInfo> <variances> <lineItems> <sql:query>
SELECT
bin.bgt_itm_id,
bi.bgt_itm_desc,
CASE
WHEN SUM(es.value) IS null THEN SUM(bin.value)
ELSE SUM(bin.value) - SUM(es.value)
END AS "cvActual",
CASE
WHEN SUM(es.value) IS null THEN null
WHEN SUM(bin.value) = 0 THEN null
WHEN SUM(es.value) = 0 THEN null
ELSE ((SUM(bin.value) - SUM(es.value))/SUM(bin.value) )*100
END AS "cvPercent"
FROM
v_budget_item_normalized bin
FULL OUTER JOIN v_cost_summary es
on bin.bgt_itm_id = es.bgt_itm_id AND
bin.month_num = es.month_num AND
bin.bgt_fy = es.bgt_fy
INNER JOIN budget_item bi
on bin.bgt_itm_id = bi.bgt_itm_id
WHERE
bin.month_num <= @reportmonth AND
bin.bgt_fy = @reportyear AND
bi.bgt_id = @budgetid AND
(
bin.value > 0 OR
es.value > 0
)
GROUP BY
bin.bgt_itm_id,
bi.bgt_itm_desc
order by bin.bgt_itm_id
FOR XML raw
</sql:query> </lineItems> <totals> <sql:query>
SELECT
CASE
WHEN SUM(es.value) IS null THEN SUM(bin.value)
ELSE SUM(bin.value) - SUM(es.value)
END AS "cvActual",
CASE
WHEN SUM(es.value) IS null THEN null
WHEN SUM(bin.value) = 0 THEN null
WHEN SUM(es.value) = 0 THEN null
ELSE ((SUM(bin.value) - SUM(es.value))/SUM(bin.value) )*100
END AS "cvPercent"
FROM
v_budget_item_normalized bin
FULL OUTER JOIN v_cost_summary es
on bin.bgt_itm_id = es.bgt_itm_id AND
bin.month_num = es.month_num AND
bin.bgt_fy = es.bgt_fy
INNER JOIN budget_item bi
on bin.bgt_itm_id = bi.bgt_itm_id
WHERE
bin.month_num <= @reportmonth AND
bin.bgt_fy = @reportyear AND
bi.bgt_id = @budgetid AND
(
bin.value > 0 OR
es.value > 0
)
FOR XML raw
</sql:query> </totals> </variances></budgetVariance>
However, to the consuming application, the SQLXML implementation behaves exactly as a normal Web page would. It takes three parameters in the URL:
Budgeted
Reportmonth
Reportyear
As an example, within the FabriKam virtual machine, navigating to this URL as a Project Manager:
http://fabrikam1:8080/budgetvariance/template/template.xml?budgetid=1&reportmonth=12&reportyear=2004
will return the following XML:
<budgetVariance xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<baselineInfo>
<projectInfo>
<row projecttitle="Project Contoso Hotels" startDate="4/7/2004" endDate="4/7/2006" summary="Contoso Hotels is a budding high-end hotel chain that is looking to outfit their hotels with custom furniture. Our goal is to convince them FabriKam International is the way to go." justification="N/A" />
</projectInfo>
<totalBudget>
<row totalBudget="441000" />
</totalBudget>
<costToDate>
<row costToDate="411803.25" />
</costToDate>
</baselineInfo>
<variances>
<lineItems>
<row bgt_itm_id="1" bgt_itm_desc="Sales PM visits to client and initial project setup" cvActual="3150" cvPercent="14.31" />
<row bgt_itm_id="2" bgt_itm_desc="Foreman & Lead Designer" cvActual="500" cvPercent="1.25" />
<row bgt_itm_id="3" bgt_itm_desc="Factory Floor" cvActual="15410" cvPercent="9.63" />
<row bgt_itm_id="4" bgt_itm_desc="Travel to client" cvActual="6025" cvPercent="30.12" />
<row bgt_itm_id="5" bgt_itm_desc="Raw materials" cvActual="2499.11" cvPercent="1.56" />
<row bgt_itm_id="6" bgt_itm_desc="Research & Development" cvActual="300" cvPercent="1.5" />
<row bgt_itm_id="7" bgt_itm_desc="Develop marketing martial for new product line" cvActual="0" cvPercent="0" />
<row bgt_itm_id="8" bgt_itm_desc="Printing & Distributing marketing material" cvActual="500" cvPercent="10" />
<row bgt_itm_id="9" bgt_itm_desc="Develop SharePoint site to share R&D ideas with client" cvActual="1000" cvPercent="40" />
<row bgt_itm_id="10" bgt_itm_desc="Purchase new server for SharePoint site" cvActual="-187.36" cvPercent="-4.68" />
</lineItems>
<totals>
<row cvActual="29196.75" cvPercent="6.62" />
</totals>
</variances>
</budgetVariance>
For detailed information on using template files with SQLXML 3.0 see Executing Template Files Using HTTP in the SQLXML 3.0 Books OnLine.