Getting XML formatted results From SQL 2000

Question

Post reply

Getting XML formatted results From SQL 2000

Jason Tontz

SSCommitted

Points: 1957
More actions
March 25, 2009 at 6:25 pm

#384212

I am attempting to expand on the methods posted by Paul Clancy in his 2009/02/20 article "Sending scheduled SQL query results via HTML e-mail using SSIS" which is one of the most helpful articles I have ever read. We want to perform the exact same steps for some data in our shop the only difference is that we still use SQL 2000 so some of the coding doesn't work. In the article, setting the root was easy using the ROOT('Order') switch in the FOR XML statement in SQL 2005 but how do we do it in SQL 2000?
I am at the step where the SQL task is handing off the results to the XML task which is formatting it using the XLST file. It apparently breaks because my XML data is returned without a hierarchy that the XLST can apply. I know that when I get past this little hurdle, I will be able to complete this project.
The Query:
SELECT CustomerID, CompanyName, ContactName,Phone from Customers
WHERE CustomerID IN ('ALFKI','ANATR','BERGS')
FOR XML AUTO, ELEMENTS
The Results:
You will note that there is no ROOT element. How can we solve this issue to be able to pass it into the XLST?
Please Advise.
Thank You In Advance.
J.T.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Would it help just to wrap the root elements around your select stement?

select '{root}' + (SELECT name, description from Orders FOR XML AUTO, ELEMENTS) + '{/root}'

Note: The xml doesn't show up on the post unless the > and < are replaced.... If the answer above doesn't help you please attach the xml output as a file together with table definition for Orders table and some test data. The format of the data you'd provide should allow us to copy and paste it into SSMS ready to run.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Add a fake table on top of your query:

select RootName, Name

from (select NULL as RootName) ROOT

INNER JOIN sysobjects ON xtype = 'U'

FOR XML AUTO, ELEMENTS

_____________
Code for TallyGenerator

Jason Tontz SSCommitted Points: 1957 More actions · Answer 3

Here is an update... Still not resolved however.

I am getting results back with an embedded hierarchy in XML format. Now I am getting a compile error OR only the Header in the HTML file when the task that creates the HTML file using the XSL template runs. I have manipulated the query to return the results in the same structure as the example. Any help with this is greatly appreciated.

XML RESULTS from Query:

{ROOT}

{oh}

{Empno}000001{/Empno}

{p}

{EmpName}David

Donaldson{/EmpName}

{peDuedate}2009-09-10T00:00:00{/peDuedate}

{EvalType}Anualrvw{/EvalType}

{/p}

{/oh}

{oh}

{Empno}000002{/Empno}

{p}

{EmpName}George

Thompson{/EmpName}

{peDuedate}2009-12-24T00:00:00{/peDuedate}

{EvalType}Anualrvw{/EvalType}

{/p}

{/oh}

{oh}

{Empno}000003{/Empno}

{p}

{EmpName}Victoria

Knudsen{/EmpName}

{peDuedate}2009-09-29T00:00:00{/peDuedate}

{EvalType}9 Months{/EvalType}

{/p}

{/oh}

{/ROOT}

XSL Template File Contents:

{?xml version="1.0"?}

{xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"}

{xsl:template match="ROOT"}

{html}

{head}

{style type="text/css"}

body {margin-left: 20px; font-family: segoe ui, tahoma, sans-serif;}

h1{color: #f00;}

h2{size: 70%; color: #00f;}

td{padding-right: 10px;}

{/style}

{/head}

{body}

{h1}Performance Evaluations Not Completed{/h1}

{xsl:apply-templates select="oh"/}

{/body}

{/html}

{/xsl:template}

{xsl:template match="oh"}

{DIV style="margin-bottom:20px;"}

{h2}Performance Reviews{/h2}

{table}

{tr}

{th}Employee Number{/th}

{th}Employee Name{/th}

{th}Performance Evaluation DueDate{/th}

{th}Evaluation Type{/th}

{/tr}

{td}{xsl:value-of select="EmpNo"/}{/td}

{xsl:apply-templates select="p"/}

{/table}

{/DIV}

{hr /}

{/xsl:template}

{xsl:template match="p"}

{div style="color:#f00; font-size: 90%;"}

{tr}

{td}{xsl:value-of select="EmpName"/}{/td}

{td}{xsl:value-of select="peDuedate"/}{/td}

{td}{xsl:value-of select="EvalType"/}{/td}

{/tr}

{/div}

{/xsl:template}

{/xsl:stylesheet}

HTML File Results (when successful):

{html}

{head}

{META http-equiv="Content-Type" content="text/html; charset=utf-8"}

{style type="text/css"}

body {margin-left: 20px; font-family: segoe ui, tahoma, sans-serif;}

h1{color: #f00;}

h2{size: 70%; color: #00f;}

td{padding-right: 10px;}

{/style}

{/head}

{body}

{h1}Performance Evaluations Not Completed{/h1}

{/body}

{/html}

Thanks In Advance. And thanks to those who have already participated in this thread.

Jason Tontz SSCommitted Points: 1957 More actions · Answer 4

UPDATE - COMPLETED!:-)

First thanks to everyone who replied to this thread it was extremely helpful.

The solution appeared to be in tags I was using. Apparently "ROOT" is not a good choice for a tag as well as "Orders" as is in the example. The use of these tags and XSL match statements were conflicting in other parts of the process (Orders is a keyword in T-SQL ((2000)) and ROOT has some special meaning in XML).

Anyways once I used a fabricated TAG such as "Homer" everything fell into place and the process started working.

SELECT Homer.CompletedOn,oh.Empno, p.EmpName, p.peDuedate, p.EvalType

FROM @var_Table Homer INNER JOIN @var_Table oh

ON Homer.EmpNo= oh.EmpNo

JOIN @var_Table p ON p.EmpNo=oh.Empno

WHERE p.CompletedOn IS NULL

for xml AUTO, ELEMENTS

This gave me a ROOT TAG of "Homer" and the subTAG of "oh" and the data elements under "p". (To match the example).

The XSL file contains:

{?xml version="1.0"?}

{xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"}

{xsl:template match="Homer"}

{html}

{head}

{style type="text/css"}

body {margin-left: 20px; font-family: segoe ui, tahoma, sans-serif;}

h1{color: #f00;}

h2{size: 70%; color: #00f;}

td{padding-right: 10px;}

{/style}

{/head}

{body}

{h1}Performace Reviews{/h1}

{xsl:apply-templates select="oh"/}

{/body}

{/html}

{/xsl:template}

{xsl:template match="oh"}

{DIV style="margin-bottom:20px;"}

{h2}Performance Evaluation Needed for Employee ID: {b}{u}{xsl:value-of select="Empno"/}{/u}{/b}{/h2}

{table}

{tr}

{th}{u}Employee Name:{/u}{/th}

{th}{u}Review Due Date:{/u}{/th}

{th}{u}Evaluation Type:{/u}{/th}

{/tr}

{xsl:apply-templates select="p"/}

{/table}

{/DIV}

{hr /}

{/xsl:template}

{xsl:template match="p"}

{div style="color:#f00; font-size: 90%;"}

{tr}

{td}{xsl:value-of select="EmpName"/}{/td}

{td}{xsl:value-of select="peDuedate"/}{/td}

{td}{xsl:value-of select="EvalType"/}{/td}

{/tr}

{/div}

{/xsl:template}

{xsl:template match="NoRecords"}

{html}

{head}

{style type="text/css"}

body {margin-left: 20px; font-family: tahoma, sans-serif;}

{/style}

{/head}

{body}

{h1}Performance Reviews{/h1}

{div}

No Performance Reviews are pending.

{/div}

{/body}

{/html}

{/xsl:template}

{/xsl:stylesheet}

Thanks Again!