Field Order in XML generated from Stored Procedure

Question

Field Order in XML generated from Stored Procedure

groverparkgeorge

Old Hand

Points: 384
More actions
October 26, 2016 at 10:38 am

#77485

I apologize if this has been covered, but I couldn't find a reference that fits.
I have been asked to support an existing web site--the original developer is out of the country and unavailable.
In the C# code that generates the aspx page, an XML file is generated from a Stored Procedure and displayed in a page, using that page's Load event.
I was asked to add one more field to the XML, which I did.
Now, however, the sequence in which the fields appear (Left to right) in the XML rows is no longer the same as the sequence in the Stored Proc. In one way, it's not crucial because the values are there, but it's not what the client wants.
The XML is generated with: for xml auto
The documentation I've found suggests that auto is not reliable in this regard, so I'm not too surprised.
Is there anything I can do to force the specific field order the client wants? I suspect I could add tags to support for xml explicit, if that's what it's going to take.
Thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 1

groverparkgeorge (10/26/2016)
I apologize if this has been covered, but I couldn't find a reference that fits.
I have been asked to support an existing web site--the original developer is out of the country and unavailable.
In the C# code that generates the aspx page, an XML file is generated from a Stored Procedure and displayed in a page, using that page's Load event.
I was asked to add one more field to the XML, which I did.
Now, however, the sequence in which the fields appear (Left to right) in the XML rows is no longer the same as the sequence in the Stored Proc. In one way, it's not crucial because the values are there, but it's not what the client wants.
The XML is generated with: for xml auto
The documentation I've found suggests that auto is not reliable in this regard, so I'm not too surprised.
Is there anything I can do to force the specific field order the client wants? I suspect I could add tags to support for xml explicit, if that's what it's going to take.
Thanks in advance.

Quick suggestion, use FOR XML PATH, easier, more readable and less cumbersome than EXPLICIT

😎

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Eirikur Eiriksson (10/26/2016)
groverparkgeorge (10/26/2016)
I apologize if this has been covered, but I couldn't find a reference that fits.
I have been asked to support an existing web site--the original developer is out of the country and unavailable.
In the C# code that generates the aspx page, an XML file is generated from a Stored Procedure and displayed in a page, using that page's Load event.
I was asked to add one more field to the XML, which I did.
Now, however, the sequence in which the fields appear (Left to right) in the XML rows is no longer the same as the sequence in the Stored Proc. In one way, it's not crucial because the values are there, but it's not what the client wants.
The XML is generated with: for xml auto
The documentation I've found suggests that auto is not reliable in this regard, so I'm not too surprised.
Is there anything I can do to force the specific field order the client wants? I suspect I could add tags to support for xml explicit, if that's what it's going to take.
Thanks in advance.
Quick suggestion, use FOR XML PATH, easier, more readable and less cumbersome than EXPLICIT
😎

+1 on Eirikur's suggestion: much "nicer" code using the FOR XML PATH syntax than the god-awful FOR XML EXPLICIT syntax.

That said you've basically identified the issue, which is that the order (of the physical columns OR the rendering) isn't guaranteed if you use wildcards. Unless you explicitly enumerate out the columns in the order you want them in, chances are you will end up with the issue you're describing.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

groverparkgeorge Old Hand Points: 384 More actions · Answer 3

Hmm. The columns in the stored proc are all named, no wildcards are used. I will try for xml path.

Thanks.

George

groverparkgeorge Old Hand Points: 384 More actions · Answer 4

That's much closer. Still a bit of tweaking, I think.

Thank you.

George

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 5

groverparkgeorge (10/26/2016)
That's much closer. Still a bit of tweaking, I think.
Thank you.
George

Good stuff, just ping back here if you run into any problems

😎

groverparkgeorge Old Hand Points: 384 More actions · Answer 6

groverparkgeorge

Old Hand

Points: 384

October 26, 2016 at 1:40 pm

#1908990

I surely will.

Thanks.