Solved

Alternative to complex FOR XML explicit

Posted on 2004-10-04
8
381 Views
Last Modified: 2013-11-19
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
Comment
Question by:svid
  • 3
  • 3
  • 2
8 Comments
 
LVL 9

Expert Comment

by:SoftEng007
ID: 12220852
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
 
LVL 5

Author Comment

by:svid
ID: 12220909
Thanks. We are (unfortunately!) still on VB6. Do you have any suggestions for this env?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12221914
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 5

Author Comment

by:svid
ID: 12221934
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12222068
It depends on your definition of "better", but IMHO the answer is no.
0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 12222574
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
 
LVL 9

Accepted Solution

by:
SoftEng007 earned 125 total points
ID: 12222586
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
 
LVL 5

Author Comment

by:svid
ID: 12226638
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Loops and updating in SQL Query 9 26
SQL Query assistance 16 25
SQL Server Import/Error Wizard error 12 19
sql server query 6 9
Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question