Thursday, March 22, 2012

Custom xml format with FOR XML EXPLICIT

I'm working on Sql Server 2005 xml capabilities, but I'm not able to obtain what I want.

Let's say I have a table with 10 fields: Field1, Field2, ..., Field10.

I would, with a "FOR XML" clause, obtain an xml document like this one:

<MyTable>
<Field1>F1value</Field1>
<Field2 Field3="F3value">F2value</Field2>
</MyTable>
<MyTable>
...
</MyTable>

I think correct way is with the "EXPLICIT" mode, but I'm not able to find the syntax to make Field3 become an attribute of the element generated from Field2.

Does someone knows if it's possible and, if it is, how?

Any help will be truly appreciated.

Easier to do this

select Field1 as "Field1",
Field3 as "Field2/@.Field3",
Field2 as "Field2"
from MyTable
for xml path('MyTable')

|||Thank you so much!

This is exactly what I was looking for.sql

No comments:

Post a Comment