• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

Alternative to complex FOR XML explicit

I need to create an XML output file from a SQL Server database, the schema for which is available. My solution (and it works perfectly) was to create a sp with select ... FOR XML EXPLICIT and then create the file. However because of the innumerable nodes and tags involved, the select statement is very lengthy and looks very complicated. However once the select statement is written, the code around is very simple (VB 6). I considered using MSXML, but the amount of code to be written would be huge.

My question is is there an elegant solution to this issue? Everybody keeps asking if I can "plug" the xsd and "make a miracle happen" without writing any code and I am not sure of the answer.

TIA.
0
svid
Asked:
svid
  • 3
  • 3
  • 2
1 Solution
 
SoftEng007Commented:
I had a similar issue where the data in the database was not xml friendly. Lots of tables to get to just to create one node!  great normalization for a database but nor very nice when using XML Explicit!

I ended up building a .Net application that is called via sp_cmdshell. We build the empty xml documnet using DOM and then load the elements and attributes via various database recordsets. Not the prettiest but fairly quick,very reliable and easy to use from sql server procs.

HTH

SoftEng007
0
 
svidAuthor Commented:
Thanks. We are (unfortunately!) still on VB6. Do you have any suggestions for this env?
0
 
Anthony PerkinsCommented:
You have written the SELECT ... FOR XML EXPLICIT query and the VB6 code.  What is missing or am I not seeing the full picture?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
svidAuthor Commented:
Nothing is "missing". The current solution works. I am just trying to make it seem less complicated because the FOR XML EXPLICIT query is extremely lengthy (the schema is complex) and I would like to know if there is a better way of doing it.

Thanks!
0
 
Anthony PerkinsCommented:
It depends on your definition of "better", but IMHO the answer is no.
0
 
SoftEng007Commented:
sorry for misreading the question.

If you managed to get FOR XML EXPLICIT to work for you and the speed is with in your specs then
you have finished. No one ever said a good solution would look simple.
Elegant solutions come in all shapes and sizes.

"If it ain't broke, Don't fix it!"
0
 
SoftEng007Commented:
FYI.
you can call any executable with xp_cmdShell
you could build the solution in VB6 and use DOM just like I did in .NET
0
 
svidAuthor Commented:
Thanks both. My question was only to see if there are other ways. I will stick to the current solution, at least for now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now