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

Custom Formatting SQLXML Result

Here's what am doing:
I have a couple of SPs that can only return data in the format i want when i use FOR XML EXPLICIT.
The problem is if am using SQLXML then the data cannot be returned as XML since formatting is done by the client (which only gives me the option of raw/nested) and therefore the result i get is not formatted as i need it.
Is there a way i can format the result from the SP in the same way as if i were just using FOR XML EXPLICIT on the server side.

Example of output formatting expected:
<Patients_List>
<patient>
<Field name="forename"/>
<Field name="surname">Patience</Field>
</patient>
<patient>
<Field name="forename">Adam</Field>

<Field name="surname">Sandler</Field>
</patient>
...
</Patients_List>

My problem is, the raw/nested options cannot format the output as above (using FOR XML EXPLICIT could achieve this).

Any suggestions?

Are you using SQL2005? If so please give me your table structure. I might help you to write FOR XML RAW/nested query.|||Am actually using SQL 2000.

sample table structure.

Table Name: pat_info

id int
surname varchar(35)
forename varchar(35)
dob datetime
occupation varchar(100)

not sure if the structure helps (its just part of table).

|||

Hello,

You can use XPath query over annotated xsd schema (which internally translates into FOR XML EXPLICIT queries) to get the desired results.

Hope this helps,

Monica

|||

Hello,

You can use XPath query over annotated xsd schema (which internally translates into FOR XML EXPLICIT queries) to get the desired results.

http://msdn2.microsoft.com/en-us/library/ms171802.aspx

Hope this helps,

Monica