• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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