question

santhosh avatar image
santhosh asked

Problem in generating XML from SQL server

Hello All, I am getting the Output XML as below, root forms id="1" name="Kontakta oss2" **form formInstanceId="B4506D1B-03F8-4BEB-883A-2D6DB854573A"** info id="1" name="Förnamn" value="Gopi" /> info id="2" name="Efternamn" value="Chandar" /> info id="3" name="City" value="London" /> info id="12" name="Telefonnummer" value="+654321321" /> info id="13" name="Comments" value="test" /> info id="14" name="Land" value="Sverige" /> /form I need the output to be Like root> forms id="1" name="Kontakta oss2"> **form>** info id="1" name="Förnamn" value="Gopi" /> info id="2" name="Efternamn" value="Chandar" /> info id="3" name="City" value="London" /> info id="12" name="Telefonnummer" value="+654321321" /> info id="13" name="Comments" value="test" /> info id="14" name="Land" value="Sverige" /> /form> /forms> The sQL is SELECT forms.formTypeId as id, forms.[name], form.formInstanceId, info.id, info.name, info.value FROM #Temp info INNER JOIN FormsType forms ON forms.FormTypeId=info.formtypeId AND forms.Deleted=0 INNER JOIN Forms form ON form.FormTypeId=forms.Formtypeid AND forms.Deleted=0 AND form.formInstanceId=info.formInstanceId AND info.name IS NOT NULL ORDER BY info.formInstanceId,form.formInstanceId FOR XML AUTO,ROOT('root') Please help me out on this. Any help would be highly appreciated.
sql-server-2005
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Can someone reformat the XML & SQL fragments so they are readable? I don't have the power! Cheers...
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
The only thing I can see there is that you've got the formInstanceID field which you say you don't want... Try omitting it from the SELECT list - you can still ORDER on it and JOIN on it.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

santhosh avatar image
santhosh answered
Hello ThomasRushton, Thanks for your reply, I tried by omitting formInstanceID in the select statement but i am not getting the **form>** node in the XML. Thanks,
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@santhosh As you already discovered, if you omit some information from the **form** then you will inevitably loose its respective tag. This is because the engine will not be able to differentiate between different instances of the form to figure that your **info** nodes have to be grouped by their respective **form**. I believe that it is possible to achieve what you need by using **for xml explicit**, which is much more cumbersome to write then **for xml auto**. I can try to play with **for xml explicit** to see if I can get the shape you need.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.