SQL Server to XML export - SQL Server Forums

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

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

SQL Server to XML export

Author

Topic

vaberonica
Starting Member

7 Posts

Posted - 2005-03-18 : 10:42:35

Hi all,

I was just wondering if it was possible (maybe somehow using a DTS) to export a SQL Server 2000 database to (either 1 or preferably many) XML file(s)?

Maybe this is impossible but i'd like to find out sooner rather than later so i can get cracking on code if that is what is needed.

Thanks a mill.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-18 : 12:26:47

Q, why to XML files and not csv ?

rockmoose

vaberonica
Starting Member

7 Posts

Posted - 2005-03-21 : 06:22:36

I dont want csv files because these xml files will hopefully be generated as part of a scheduled job and transferred to a different server where they will be used as the database there. I have a c# web application that will read these xml files.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-21 : 08:02:16

OK, now the question is why bother creating and moving the XML files? Why not have your application query the database directly?

vaberonica
Starting Member

7 Posts

Posted - 2005-03-22 : 07:35:09

I know what you're thinking - you think its crazy to do all this when i have a SQL Server database with the information already there. I do have my reasons.

One of the reasons i want to do this (other than to see if its possible) is because ther SQL Server database is on a different server to that of the webapplication. The web application only needs certain information from this database - about 2 tables in total. The database is already in use in another application and there are a number of unresolved performance issues owing to where the database is hosted. The webapplication as a result is slow and not always available.

I have created and uploaded the information i need as part of a VBA Access project but this is a manual process - the user has to go in and click a button to do it. A DTS would just be a nice way of doing it as i could schedule the task to run every hour or so and have the information in the xml files up-to-date.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-22 : 16:05:27

So why not use the XML option in the SELECT query and have DTS or whatever output the results to a file?

Tara

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-22 : 16:08:45

quote:
Originally posted by tduggan

So why not use the XML option in the SELECT query and have DTS or whatever output the results to a file?

I think there were truncation issues, but I might be completely wrong...

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-03-22 : 16:35:02

SELECT * FROM xxx FOR XML is output as a "stream", but I don't know how DTS handles Streams ...

Kristen

vaberonica
Starting Member

7 Posts

Posted - 2005-03-24 : 07:06:47

I've now created a stored procedure that uses the FOR XML EXPLICIT (was hoping there was a nice way of a DTS doing it for me some how).

The problem is now that when i execute the stored procedure in Query Analyser its all fine and dandy but when i use it in the DTS i'm getting the following error.

Error Description: FOR XML EXPLICIT query contains column name "CAT_ID". Use
the TAGNAME!TAGID!ATTRIBUTENAME format where TAGID is a positive integer.

The thing is this CAT_ID is part of the INNER JOIN clause that joins 2 tables based on this CAT_ID. Surely the TAGNAME!TAGID!ATTRIBUTENAME format should not be used for this?

Its really bugging me because there was no problem with it in the query analyser.

As you can tell i'm new to the whole XML game so if anyone has any ideas i would be very grateful.

Thanks

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-24 : 07:18:59

Ok,

Why don't You do something like this and just bcp the data out:

SELECT
	'<authors>'
	+'<au_id>'+LTRIM(au_id)+'</au_id>'
	+'<au_lname>'+LTRIM(au_lname)+'</au_lname>'
	+'<au_fname>'+LTRIM(au_fname)+'</au_fname>'
	+'<phone>'+LTRIM(phone)+'</phone>'
	+'<address>'+LTRIM(address)+'</address>'
	+'<city>'+LTRIM(city)+'</city>'
	+'<state>'+LTRIM(state)+'</state>'
	+'<zip>'+LTRIM(zip)+'</zip>'
	+'<contract>'+LTRIM(contract)+'</contract>'
	+'</authors>' AS xmlstuff
FROM
	pubs..authors

btw,
There is an official xml sucks thread

rockmoose

vaberonica
Starting Member

7 Posts

Posted - 2005-03-24 : 08:08:39

Thanks for that Rockmoose. It works nicely ..... except if one of the values is NULL, then the whole string comes back as NULL. There are a number of fields that can be null in this query. Its probably a very easy thing to fix but i just cant think of it right now.

Read that thread btw, and i have to say those who hate XML (myself included) really hate it and those who love it seem to want to have its babies (i think those people need to get out more) :p

Thanks for all your help so far - thats for everyone who replied.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-24 : 08:10:51

For any column that might be null:

SELECT... +'<au_id>'+IsNull(LTRIM(au_id), '')+'</au_id>'

vaberonica
Starting Member

7 Posts

Posted - 2005-03-24 : 08:17:49

You legend - thanks a million.

Give a person a fish and you feed them for a day; teach that person to use the Internet and they won't bother you for weeks.

vaberonica
Starting Member

7 Posts

Posted - 2005-03-24 : 08:28:06

I'm sorry for annoying everyone but this XML sh*t is annoying me. Now that the DTS runs doing the query the way Rockmoose suggested it, i still have a problem.

Both Query Analyser and the DTS both truncate the data so half of it is missing. Is there a max length a stream can be or something?

Give a person a fish and you feed them for a day; teach that person to use the Internet and they won't bother you for weeks.

Kristen
Test

22859 Posts

Posted - 2005-03-24 : 08:41:47

You might prefer

SELECT... + IsNull('<au_id>'+ LTRIM(au_id) +'</au_id>', '')

i.e. the XML tag + data is missing altogether if the column is null (rather than being treated as an empty field)

"Both Query Analyser and the DTS both truncate the data so half of it is missing. Is there a max length a stream can be or something?"

QA has a limit of 8000 characters per column (default is less, but you can raise it in that in th Options)

Dunno about width limits on DTS.

Can you spread the XML over multiple columns? If so then output each as a column, and don't concatenate them.

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-24 : 08:43:43

You are probably breaking the 8000 character limit of the (var)char datatype.

If you are using .NET You might also consider this method:
DataSet ds;
// load the dataset with your data somehow...
ds.WriteXml("myxmlfile.xml",XmlWriteMode.IgnoreSchema);

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-24 : 08:52:39

Also watch out for blank space that You might not need:

select '<bla>' + rtrim(ltrim(' nospace ')) + '</bla>'
select '<bla>' + ltrim(' space ') + '</bla>'

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-03-26 : 00:42:55

Based on that Rockey my approach, based on ROb's approach, is going to get get really tedious to type for each column:


SELECT... + IsNull('<au_id>'+ NullIf(RTRIM(LTRIM(au_id)), '') +'</au_id>', '')

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-26 : 03:15:20

quote:
Originally posted by Kristen

Based on that Rockey my approach, based on ROb's approach, is going to get get really tedious to type for each column

You weren't actually going to type that were You kristen?
This sort of stuff You generate from IINFORMATION_SCHEMA.COLUMNS

rockmoose

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-26 : 06:49:59

And you don't want NullIf, you want IsNull.

Kristen
Test

22859 Posts

Posted - 2005-03-26 : 10:19:17

"You weren't actually going to type that were You kristen"

No, but its a bit of a stretch for a novice ... writing SQL to write SQL and all that!

"And you don't want NullIf, you want IsNull"

I've got IsNull where I need IsNull, but I've also got NullIf where I want NullIf !

(And, personally, I would have used COALESCE, but I cut&pasted your sample to save some typing ...)


DECLARE	@au_id varchar(10)
SELECT	@au_id = 'FOO'
SELECT	IsNull('<au_id>'+ NullIf(RTRIM(LTRIM(@au_id)), '') +'</au_id>', '')
SELECT	@au_id = NULL
SELECT	IsNull('<au_id>'+ NullIf(RTRIM(LTRIM(@au_id)), '') +'</au_id>', '')

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources