How to Create an XML in MS SQL Server 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to Create an XML in MS SQL Server 2000

Author

Topic

kurtzky
Starting Member

2 Posts

Posted - 2009-04-28 : 01:51:38

I have to create a stored procedure that will create/generate an XML for a selected set of data (queried from a table). Any idea or references on how to do it, particularly in coding the Tags and the contents?? Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-28 : 07:02:57

Check Books Online, particularly the "FOR XML" clause. Should do all that you need.

kurtzky
Starting Member

2 Posts

Posted - 2009-04-28 : 20:17:53

Ok. Here is the sample data that I'm trying to display in the XML.

USE pubs
SELECT TOP 15
ti.type, a.au_lname + ' ' + a.au_fname as Author,
a.phone, a.address, a.city,
ti.title_id, ti.title, ti.price
FROM authors a
INNER JOIN titleauthor t
ON a.au_id = t.au_id
LEFT JOIN titles ti
ON t.title_id = ti.title_id
ORDER BY ti.type, Author

The XML format to be generated should be like this.

http://www.geocities.com/reggae_blur/sample.xml

In that example, for every BookType, there are different authors, author details, books, and book details available. Let's say for type "Business", there are two authors. These authors can have more than one book (see author Marjorie Green).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources