SQL Server and generatd xml with xml auto, elements.

Question

I'm working on an app that takes data from our DB and outputs an xml file using the FOR XML AUTO, ELEMENTS on the end of the generated query, followed by an XSLT to transform it the way we want. However in a particular case where we are generating some data using an sql scalar function, it always puts that element into a sub-table node named the same as the table node it's already in (so say it's a table xyz, it'd be print("<xyz><node1></node1><xyz><generated-node-from-function></generated-node-from-function></xyz>");

No matter what I try (even directly manipulating a copy of the sql as generated by the app) it always seems to create this extra node layer, which causes problems later when we try to process this xml to extract the data later. Is there any particular property causing the xml generator in sql server to work this way, and is there any way to prevent it so I can keep the generated data node on the same level as the rest of the data for the table it's associated with?

Edit: renamed columns/tables in some cases but otherwise should be the same sql.

SELECT * FROM (SELECT column1,column2,column3,iduser,jstart,jstop,jbatchperiod,jinactive,processed,column4,lock,column5,batchticketmicr,machineid,sjobopex,szopexrefid,jreceived,jstartopex,jstopopex,idspecialmicr,idp2batchoriginal,stateflags,bcrossrefid,bidentifier1,bidentifier2,bidentifier3,bidentifier4,bidentifier5,idexport,idimport,rsahash FROM table1) table1
  LEFT JOIN (SELECT column21,ienvelope,isort,column1,idtemplate,processed,column4,lock,envelopetypecode,szqueuesvisitedunique,exportdate,jcompleted,status,ipriority,idbankaccount,iprioritybeforerzbump,fstoredrecondata,cscountyid,column10,column11,checkbox1,checkbox2,column12,column13,column14,xxxempfein,column15,column16,originalenvelopeid,column17,column18,xxxoag,trackingnumber,csldc,ecrossrefid,postmark,routingflags,eidentifier1,eidentifier2,eidentifier3,eidentifier4,eidentifier5,idexport FROM envelope) envelope ON table1.column1=Envelope.column1
  LEFT JOIN (SELECT column21,column22,isort,column23,processed,side,pagetypecode,rawmicrline,rawscanline,rawbarcode,exportid,szlocandconf,szlocandconfpagefields,idformtemplate,szparms,rawmarksense,audittrail,audittrailelectronic,pixheight,pixwidth,ocrattemptcounter,idspecialmicr,idpageexception,pagemodifierflags,column10,csldc,rejectdate,rejectuser,rejectqueue,fsupervisorreject,xxxempno,xxxtraceno,xxxemplcnt,checkbox1,keyword,templatealtered,templateflags,pidentifier1,pidentifier2,pidentifier3,pidentifier4,pidentifier5,isscanlinevalid,idexport,clickcount FROM Table2) Table2 ON Envelope.column21=Page.column21
  LEFT JOIN (select column22, column21, dbo.Fileimagepath(column21, column22) as path from Table2) Fileimg ON Table2.column21=FileImg.column21 AND Table2.column22=FileImg.column22
 WHERE Envelope.column21 = 8
 FOR XML AUTO, ELEMENTS

Another edit: basically FileImg's results are getting wrapped in an extra set of Table2 tags inside existing table2 tab with the rest of the data.

Yet another Edit: Testing against another database with the same sql it worked correctly, it appears there is a bad setting in my database or the stored proc is different goes to investigate farther.

If that doesn't work I'll try some of the other suggestions above, thanks for the help so far :)

Answer 1

I would try to get rid of the sub query parts "(SELECT..." and do regular joins, like:

SELECT table1.column1, table1.column2, ..., envelope.column21, ...
FROM   table1 LEFT JOIN envelope on table1.column1 = envelope.column1 ...
WHERE  envelope.column21 = 8
FOR XML AUTO, ELEMENTS

To be clearer I ommitted most of your columns and joins. Just insert the columns you need and the njoins which are necessary.

Does this return you the right XML or am I missing the point?

Answer 2

I can't reproduce it with one of my functions (I tried to do it the same way you do it).

But I have a suspicion: is there a SELECT query in the FileImagePath function which isn't used for the final result? Maybe this creates the artifact you are experiencing?

Try to insert

return 'test'

as first line of the function (if you can do that in your development database - or if you're the only one using that function). And see, if the behaviour changes.

Answer 3

Have a look at FOR XML EXPLICIT - it's more complicated but you define the structure as you want it.

Answer 4

Doing some further reserach, as of now it appears running a db in 2000 compat mode while on a 2005 sql server this problem is created, will not pin this until confirmed.