Outputting XML from SQL

Question

Outputting XML from SQL

neddyflanders

SSC Eights!

Points: 972
More actions
February 19, 2003 at 3:01 pm

#64769

I have a table called TempTable. It has three values in it. PeopleCode, FirstName & LastName. I am trying to create a text file that looks like the following (put arrows in to indent):
<Employee>
<PeopleCode>3</PeopleCode>
---> <FirstName>John</FirstName>
---> <LastName>Doe</LastName>
<PeopleCode>4</PeopleCode>
---> <FirstName>Harry</FirstName>
---> <LastName>Who</LastName>
</Employee>
Using this code:
Select FirstName, LastName, PeopleCode
FROM TempTable As Employee
For XML Auto, Elements
I get close but I get the following result, as you can see Employee is not the root for all the values. Any help would be appreciated:
<Employee>
<FirstName>Thomas</FirstName>
<LastName>Jefferson</LastName>
<PeopleCode>008</PeopleCode>
</Employee>
<Employee>
<FirstName>George</FirstName>
<LastName>Washington</LastName>
<PeopleCode>009</PeopleCode>
</Employee>
February 24, 2003 at 12:00 pm

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

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

reaper3310 Valued Member Points: 52 More actions · Answer 1

From what I have read, to get the root node you must create it programmatically. SQL Server won't return it. So, you would create a string like this:

'<Root>' plus 'SQL Output' plus '<Root/>'.

If there is a way to return a root node using the SQL query I would be very interested.

My information comes from "Programming Microsoft SQL Server 2000 with XML" by Graeme Malcolm. Hope this helps.

Phil C.

RyanRandall SSChampion Points: 13623 More actions · Answer 2

You can do pretty much anything with 'for xml explicit'. In this case you can do something like this...

 

create table TempTable (FirstName varchar(50), LastName varchar(50), PeopleCode int)



insert into TempTable values ('John', 'Doe', 3)

insert into TempTable values ('Harry', 'Who', 4)



select 'tag' = 1, 'parent' = null, 'Employees!1!null' = null, 'Employee!2!PeopleCode!element' = null, 'Employee!2!FirstName!element' = null, 'Employee!2!LastName!element' = null

union all

select 2, 1, null, PeopleCode, FirstName, LastName from TempTable

for xml explicit

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Rick Todd Hall of Fame Points: 3302 More actions · Answer 3

I've done a bit with the FOR XML stuff in SQL Server 2000, but honestly gave up when it came to EXPLICIT. It just seemed like too much work. Maybe I should take another look. But, I couldn't find anyway to make a properly formed XML document/snippet without it. And since most other applications tend to like the data as proper XML it seems silly that it doesn't just come out that way with the FOR AUTO statement.

I like RyanRandall's example, but for a table with 15 rows it seems like a lot of extra hassle. I decided to just return an ugly result set and let the Data Access Layer deal with the results in C#.

Rick Todd

tcartwright SSCertifiable Points: 5573 More actions · Answer 4

K, I am going to add this into a article I plan to write, but there is a way to

do what you want to do with XML AUTO. IMHO it is a LOT easier to code than using

XML EXPLICIT. I have not done a study on it to see how well it performs versus

the SAME query using XML EXLICIT. A friend of mine and I developed this method

together after he wanted to do this EXACT thing you are attempting to do.

Original solution credits go to Steve Osoba. Anyway on with the tip. Basically

what you are attempting to do can be done by using a FAKE join.

Examples (run in pubs) :

Example 1 :



USE PUBS

GO



DBCC TRACEON(257) --make the xml pretty for QA

GO



SELECT [Authors].[FHR], --Fake Header Record

[Author].[au_id], 

[Author].[au_lname], 

[Author].[au_fname], 

[Author].[phone], 

[Author].[address], 

[Author].[city], 

[Author].[state], 

[Author].[zip], 

[Author].[contract] 

FROM [pubs]..[authors] [Author]

INNER JOIN (SELECT NULL As FHR) [Authors]

ON 1 = 1

FOR XML AUTO, XMLDATA



DBCC TRACEOFF(257)

Here we add a FAKE join with a virtual table and a null value. We use the ON of

1=1, ensuring the join returns true.

More depth you ask? Example 2 :



USE PUBS

GO

DBCC TRACEON(257) --make the xml pretty for QA

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AuthorTitles]') and OBJECTPROPERTY(id, N'IsView') = 1)

drop view [dbo].[AuthorTitles]

GO



CREATE VIEW AuthorTitles

AS 

SELECT titleauthor.au_id, 

titleauthor.title_id, 

titleauthor.au_ord, 

titleauthor.royaltyper, 

titles.title, 

titles.type, 

titles.pub_id, 

titles.price, 

titles.advance, 

titles.royalty, 

titles.ytd_sales, 

titles.notes, 

titles.pubdate 

FROM pubs..titleauthor 

INNER JOIN pubs..titles

ON titleauthor.title_id = titles.title_id

GO





SELECT Authors.FHR, --Fake Header Record

Author.au_id, 

Author.au_lname, 

Author.au_fname, 

Author.phone, 

Author.address, 

Author.city, 

Author.state, 

Author.zip, 

Author.contract,

Titles.FHR, --Fake Header Record

Title.title, 

Title.type, 

Title.pub_id, 

Title.price, 

Title.advance, 

Title.royalty, 

Title.ytd_sales, 

Title.notes, 

Title.pubdate 

FROM (SELECT NULL As FHR) As Authors

INNER JOIN pubs..authors As Author

ON 1 = 1

INNER JOIN (SELECT NULL As FHR) As Titles

ON 1 = 1

INNER JOIN pubs..AuthorTitles As Title

ON Author.au_id = Title.au_id

FOR XML AUTO, XMLDATA



DBCC TRACEOFF(257)

GO

As you can see, this technique can provide some very complex XML. IMHO I do not

think that the performance will be worse than some of the more complex FOR XML

EXPLICIT queries I have seen, as they are doing usually SEVERAL unions to do

this same thing, but again I have not done a performance study.

Tim C //Will code for food

tcartwright SSCertifiable Points: 5573 More actions · Answer 5

If using ADODB to query the stored proc using a ADODB.Command object with the ADODB.Stream object, use the command.properties("xml root") to add the root for the xml. EX:

 

Cmd.properties("xml root") = "root"

Tip : you can also do the transform on the fly to using Cmd.properties("xsl")..... 😀

Tim C //Will code for food

Rick Todd Hall of Fame Points: 3302 More actions · Answer 6

Thanks TC, that info rocks.

Now I just have to remember where I was planning on using it orignally!

Rick Todd

tcartwright SSCertifiable Points: 5573 More actions · Answer 7

Thanks strick9. I'll pass the appreciation on to Steve as well. When I said

we did not do a true performance test, Steve made a minor correction, we did do

some slight testing, but it was not a test in the sense I would consider it,

with a control over a period of time, with massive amounts of iterations, and in

a controlled environment. We basically used the GETDATE and DATEDIFF to get the

millisecond differences between running the two : EXPLICIT and AUTO, and using

the QA execution plan. In our cheesy test we found that our method actually

outperformed EXPLICIT, but I am still planning on a true test. Maybe someday I

will get to it.

Tim C //Will code for food