October 7, 2003 at 7:34 am
Our business needs to provide another company with an XML document. This document will contain listing information for residential housing. This XML document has to meet a DTD.
I am new to this.
Does the DBA generate the XML document?
If so, how?
How do we meet a DTD?
(more detailed the better please)
October 8, 2003 at 2:47 am
Yes, you can do it with SQL 2000.
I would begin by search google, msdn.microsoft.com and SQL Books Online for "FOR XML", "FOR XML EXPLICIT", "FOR XML AUTO". These are the commands for a SELECT query to output in XML. This is quite a large topic though so I reccomend you researching the above first and then posting back if you have any relevant questions.
Dan
October 10, 2003 at 7:46 am
Depending on the mismatch between the data in your database, and the data requirements of the DTD, this can go from difficult to very very difficult.
On the other hand, if you can apply an XML transformation (using XSLT), this can be much easier. I'm not well versed enough in SQL Server's XML capabilities to be sure, but I have not heard of a way for a SQL programmer to apply an XSLT.
I'm an architect. As such, I would view the job of retrieving the CORRECT information in a reliable and secure manner the job of the database (and the DBA). Transforming the data to meet a data requirement is the job of a programmer.
That said, you may (or may not) make the programmer's life easier if you return the data in XML format as input to the transformation. That depends on the tools your developer is using. (e.g. too general of a topic for this response).
October 10, 2003 at 7:58 am
Yes, its a fairly easy job to get the data out in XML format from SQL Server, I would then pass it through an XSLT to meet the requirements (If needed, for a simple DTD you could get away with the format from SQL Server).
October 10, 2003 at 6:18 pm
I agree with Nick, this is too general of a topic to cover here. But here is the way I would tackle this.
1: Map your database fields to the required fields in the DTD.
2: Create a form of "Web Service" that calls a stored procedure in the database that returns a recordset to the service and converts it to XML in the proper format.
While SQL Server 2000 can use the FOR XML... it is very slow. It's also a royal pain to work with the "FOR XML Explicit" queries. I have a few of them in my DB that are over 3000 lines long. Talk about a debugging nightmare!
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply