svid
asked on
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.
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.
ASKER
Thanks. We are (unfortunately!) still on VB6. Do you have any suggestions for this env?
You have written the SELECT ... FOR XML EXPLICIT query and the VB6 code. What is missing or am I not seeing the full picture?
ASKER
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!
Thanks!
It depends on your definition of "better", but IMHO the answer is no.
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!"
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!"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks both. My question was only to see if there are other ways. I will stick to the current solution, at least for now.
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