x

Extract XML from SQL 2000 with schema

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.
2 Extract data for reporting as XML, then use a second step to build into the correct format
3 Choose another piece of software to build.
4 None of the above

Thanks

more ▼

asked Dec 31, 2009 at 11:55 PM in Default

phact gravatar image

phact
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Jan 04, 2010 at 10:51 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98k 19 21 74

+1 - code is most probably the way to go here
Jan 04, 2010 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, 2010 at 05:55 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x146

asked: Dec 31, 2009 at 11:55 PM

Seen: 1170 times

Last Updated: Jan 04, 2010 at 11:20 AM