question

mikelogan avatar image
mikelogan asked

Help formatting an XML export using a SQL statement

I'm trying to export an XML file from SQL server using this code that was made for me: ( SELECT ISNULL([NAME],'') AS 'NAME' FROM [NAMES] NA WHERE RT.[ID] = NA.[ID] FOR XML PATH('NAME'), ROOT ('NAMES'),TYPE ), It should look like this: *I purposely added spaces after the less than sign to show code here < NAMES>
< NAME>BOB< /NAME>
< NAME>ROBERT< /NAME>
< NAME>BOBBY< /NAME>
< /NAMES>

But it's exporting as: < NAMES>
< NAME>
< NAME>BOB< /NAME>
< /NAME>
< NAME>
< NAME>ROBERT< /NAME>
< /NAME>
< NAME>
< NAME>BOBBY
< /NAME>
< /NAMES>
Any help is greatly appreciated.
sqlxmlexport
10 |1200

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

1 Answer

·
KenJ avatar image
KenJ answered
Remove the 'NAME' alias from the select line. It's treating the alias name as a node name: SELECT ISNULL([NAME],'') -- AS 'NAME'
4 comments
10 |1200

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

Thanks so much, but I'm such an amateur. Just remove this line altogether?
0 Likes 0 ·
No. Keep the line. I just commented out the AS 'NAME' (you can remove "AS 'NAME'" completely)
0 Likes 0 ·
PERFECT! You made my day!
0 Likes 0 ·
Glad to help.
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.