Procedure to create schema defined XML from a set of tables

Question

Procedure to create schema defined XML from a set of tables

MonkSup

SSC-Addicted

Points: 429
More actions
November 8, 2015 at 8:20 pm

#304989

I've been getting help on a related question over in a different part of this forum:
http://www.sqlservercentral.com/Forums/Topic1732430-3740-1.aspx#bm1732438
...but since this is now a 2012 specific question I thought it might be more prudent to ask it here.
I am attempting to automate the upload of data from a 2012 database to a set of external sites via XML. In a nutshell, what I need to figure out is how to export data from a 2012 database to a pre-defined XML format (.xsd schema definition supplied by the respective external site).
What is the best way to go about doing this?
I've experience as an embedded applications developer but am a bit new to RMDBS so if there are any examples people can point me to I'd much appreciate it.
Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

Play around with/get comfortable with FOR XML. There are 4 FOR XML methods, RAW, AUTO,EXPLICIT, PATH once you are comfortable with them you can export data into any XML format.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

MonkSup SSC-Addicted Points: 429 More actions · Answer 2

Thanks for your response. I've been digging around the MSDN documentation...

Is OPENXML something I should look into?

https://msdn.microsoft.com/en-us/library/ms187897(v=sql.110).aspx

I can find plenty of examples for importing and creating structures for XML - most of them in reference to the four commands in the previous post - but I'm having trouble locating an example of how to import an existing .xsd schema and then structure my XML accordingly.

Are there any MSDN (or other resources) that address this scenario specifically?

I'll keep digging...

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

MonkSup (11/10/2015)
Thanks for your response. I've been digging around the MSDN documentation...
Is OPENXML something I should look into?
https://msdn.microsoft.com/en-us/library/ms187897(v=sql.110).aspx
I can find plenty of examples for importing and creating structures for XML - most of them in reference to the four commands in the previous post - but I'm having trouble locating an example of how to import an existing .xsd schema and then structure my XML accordingly.
Are there any MSDN (or other resources) that address this scenario specifically?
I'll keep digging...

You can use BCP to import XML files (keep in mind that XSD files are XML). Note this article: https://msdn.microsoft.com/en-us/library/ms191184.aspx

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001