|
There is a change to a reporting requirement for a govt agency. Data needs to be interfaced via XML as per an XSD Schema. The data is held on an SQL 2000 database over which I have limited control. I can change the table structures and create stored procedures for SQL selects. I am a newbie to XML so I have bought the WROX XML database book. I am conversant with SQL and Visual basic. It seems that SQL2000 does not support the creation of XML schema, and that schemas work best if the elements correspond to a table. Could I ask for advice on the best way to proceed? 1 Set up a client computer with SQL2008, replicate the data and extract from the client. Thanks
(comments are locked)
|
|
You can use SQL Server 2000 to extract to a specific structure, although it won't reference the schema directly. You have to build the schema through a series of UNION joins and it's extremely ugly and difficult to maintain. But, it can be done. This is a question of where do you want the complexity to live. You can perform the query above, but it will be very difficult to build and hard to maintain, or you can do what you propose and either extract out to 2008 or use code. Personally, I'd go with the code approach. Yes, SQL Server can deal with XML, and in 2008, it's actually pretty decent at it, but it's not it's strength. I think you'd be better off and provide a more flexible and easy to maintain system by using .NET, or whatever you're coding in, to format the data as it comes out of SQL Server. +1 - code is most probably the way to go here
Jan 04 '10 at 11:20 AM
Matt Whitfield ♦♦
SQL Server is a fantastic RDBMS, but its XML features, especially when using a specified XSD, are still maturing. I would extract the data from SQL and then use code in a different language (I would use Python, but almost all modern languages have good XML libraries.)
Jan 04 '10 at 05:55 PM
TimothyAWiseman
(comments are locked)
|
