Need to use an XML schema to export data... help

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 2005 Forums

Transact-SQL (2005)

Need to use an XML schema to export data... help

Author

Topic

Maxer
Yak Posting Veteran

51 Posts

Posted - 2008-03-20 : 09:45:31

Well I have NO IDEA how to do this... so I'll just admit my ignorance up front.

Anyway I have some customer records that need to be sent off for mailing.

The printing company takes XML files.

They provided me with an XML Schema (.xsd file)

I need to take the data in my table and export it to an .xml file to send off.

I tried doing something like this at first:


WITH XMLNAMESPACES (DEFAULT 'urn:blah.blah.blah...')
SELECT *
FROM
(SELECT    CONVERT(varchar, GetDate(),120) AS submit_date, 
			'999999' AS client_billing_id, 
			customers.FirstNm AS customer_first_name, 
			customers.LastNm AS customer_last_name, 
            .....blah...additional fields..
FROM         customers
) As Offset
FOR XML RAW ('Customer'), ROOT('CustomerMailingList'), ELEMENTS

Now that worked pretty well, it gave me a file that was formatted in what appeared to be the proper XML style, and I could save it and it "looked right".

However, the printers said there were a few errors and sent back a .XSD schema file for me to use.

Sounds great... but I haven't a clue on how to use it.

I've seen a few articles on how to manage existing schemas and how to store data IN the database, but nothing on how to EXPORT data.

As I understand it I just want to take data in my customers table and use the schema as a template / file mapping document to dump it out to XML.

So... help? :)

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-20 : 09:51:34

have a look at this & see if it helps
http://msdn2.microsoft.com/en-us/library/ms191184.aspx

Maxer
Yak Posting Veteran

51 Posts

Posted - 2008-03-20 : 10:36:18

Thanks, I'm reading through that now.

This wouldn't be the sort of thing I might use integration services for is it? Or is that more for bringing data INTO the server, not dumping it out?

EDIT: That link does make sense, but I think that seems to be more about creating my own format file and exporting to various formats (XML included of course).

However, isn't there a way to take an existing XML .xsd schema file and integrate that with SQL Server so you can use that as a template for exports? (Or am I misunderstanding what the .xsd file does?)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-20 : 10:41:38

If you're using SQL 2005 you've datatype xml which you can asociate with xsd file on defining and while insertion it will validate xml against this xsd and errors if it does not comply.

Maxer
Yak Posting Veteran

51 Posts

Posted - 2008-03-20 : 10:42:45

It looks like this is what I should be doing?
http://msdn2.microsoft.com/en-us/library/ms176009.aspx

To import that existing .xsd file?

But I'm not understanding how I actually get the server to do anything with that once I have it imported in the server and the database?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources