How to Create an XML in MS SQL Server 2000
-
I have to create a stored procedure that will create/generate an XML (and a file) 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.
-
Maybe the following article will help you (Google search string: "SQL 2000 FOR XML"):
http://www.databasejournal.com/features/mssql/article.php/2196461/XML-and-SQL-2000-Part-1.htm
If you need a more detailed explanation you should provide more detailed information as well.
For help on how to do that please see http://www.sqlservercentral.com/articles/Best+Practices/61537/
-
Ok. Thanks for that. I tried it, but the tags seemed disorganized. Btw, I used BCP to write the XML file (the problem is, I got an error and the elements are not properly indented). Anyways, this should be the appearance of my XML.
Do you think I can make this format using SQL? Thanks again.
-
Please post sample data as stated in my post above (table definition, sample data ready to insert and expected output).
-
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).
-
Unfortunately, the data you provided cannot be used right away, since I don't have a database "pubs" nor the tables you referred to.
It took me a moment to reformat your result set back into the tables you used in your select clause...
One way of getting the nested xml structure is to declare it in the select clause:
SELECT TOP 15ti.type "BookType/@type",
a.au_fname "BookType/BooksOnType/Author/@f_name",
a.au_lname "BookType/BooksOnType/Author/@l_name",
a.phone "BookType/BooksOnType/Author/Phone",
a.address "BookType/BooksOnType/Author/Address",
a.city "BookType/BooksOnType/Author/City/@name",
ti.title_id "BookType/BooksOnType/Author/BooksByAuthor/@title_id",
ti.title "BookType/BooksOnType/Author/BooksByAuthor/BookTitle/@title",
ti.price "BookType/BooksOnType/Author/BooksByAuthor/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, au_lname
for xml path
-
hmm i'm using SQL 2000. I think PATH is not recognized. Btw, do you know any way on how to put this in a file? Let's say C:\Sample.xml? Thanks.
-
Hi,
I spent some more time on the issue.
Here's a solution that should work with SS2K:
SELECT TOP 15BookType.type,
(SELECT
Author.au_fname,
Author.au_lname,
Author.phone,
Author.address,
Author.city,
(Select
BooksByAuthor.title_id,
BooksByAuthor.title,
BooksByAuthor.price
FROM titleauthor t1
INNERJOIN @titles BooksByAuthor
ON t1.title_id = BooksByAuthor.title_id
WHERE t1.au_id = Author.au_id
AND BooksByAuthor.type = BookType.type
FOR XML AUTO, TYPE
)
FROM authors Author
INNER JOIN
(SELECT DISTINCT type, au_id
FROM titleauthor t1
INNER JOIN titles ti2
ON t1.title_id = ti2.title_id
) ta
ON ta.au_id = Author.au_id
WHERE ta.type = BookType.type
FOR XML AUTO, TYPE, ELEMENTS
)
FROM titles BookType
GROUP BY BookType.type
ORDER BY BookType.type
FOR XML AUTO, TYPE
The "secret" to get the tags in the right order is the use of nested SELECT statements. Please refer to BOL "Nested FOR XML Queries". The nested SELECTs also are used to format the output in a different way (with and without element tags).
The SELECT DISTINCT clause is used to avoid showing authors in book types they don't have books published for. Otherwise those authors will show up, but without any elements in the tag "BooksByAuthor".
For exporting the file please see BOL, look for "bcp Utility".
-
TYPE does not exist also in SQL 2000. But thanks for the help. :D. I'll take a look at the BOL and try to apply it on my query. I'll post more questions here soon (if there are still). Hehe. Thanks again.
-
Hi,
I'm really sorry to mess up that badly with the last two approaches. :crazy: :blush:
Final approach:
Attached please find a bcp script that copies out your data in the desired format using the (hopefully/most probably working) "FOR XML EXPLICIT" statement.
Note:
Since bcp doesn't like line feeds the select statement is somewhat hard to read...
Furthermore it is required to change the database name, since bcp is using full qualified names (open the file in an editor and replace test.. with your database name and schema).
Finally, your "Top 15" is not included since it is not clear, what "15" would refer to.
Also, if you don't insist on the xml structur in terms of elements vs. attributes maybe you could use the following code (for details on how to get the elements ordered and nested please see http://www.15seconds.com/issue/001102.htm):
SELECT DISTINCTBookType.type,
Author.au_lname,
Author.au_fname,
Author.phone,
Author.address,
Author.city,
BooksByAuthor.title_id,
BooksByAuthor.title,
BooksByAuthor.price
FROM titles BookType
INNER JOIN titleauthor ON BookType.title_id = titleauthor.title_id
INNER JOIN authors Author ON titleauthor.au_id = Author.au_id
INNER JOIN titleauthor titleauthor2 ON titleauthor2.au_id = Author.au_id
INNER JOIN titles BooksByAuthor ON BooksByAuthor.title_id = titleauthor2.title_id
FOR XML AUTO
-
Cool. Thanks for that. Btw, the BCP that you gave me, should I run it in SQL QA? 😀
-
reggae_blur (4/30/2009)
Cool. Thanks for that.I'm glad it finally worked. Again, sorry for the mass...
reggae_blur (4/30/2009)
Btw, the BCP that you gave me, should I run it in SQL QA? 😀No.
The code needs to be run from the command line.
It will create a file test.xml in the folder you're running the command from.
In order to make it runnable from QA you need to run the bcp command via xp_cmdshell.
declare @cmd varchar(8000)-- build the bcp string
select @cmd = 'bcp "SELECT 1 ''''Tag'''',NULL ''''Parent'''' ... FOR XML EXPLICIT" queryout C:\temp\test.xml -c -T'
-- make it callable via TSQL
select @cmd = 'master..xp_cmdshell ''' + rtrim( @cmd) + ''''
--check the length of the string
if len(@cmd)=8000
select 'string too long'
--Run the BCP command to move the data out.
exec( @cmd)
You probably noticed that you have to wrap the alias name in (4) single quotation marks.
-
Ok..Don't be sorry dude.. In fact u tried to help me...thanks for that. Ok, i'll try the bcp command. Thanks again! 😀
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply