Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jimoomba
Yak Posting Veteran
90 Posts |
 Posted - 2011-09-28 : 07:10:18
|
| HI All,I want the xml to be dsplayed in the below format using XML Explicit :<Root> <QueryWaiting version="B000"> <Authentication id="57985539">NQIGUTXD</Authentication> </QueryWaiting> <QueryWaiting version="B000"> <Authentication id="57985540">FONFSNPX</Authentication> </QueryWaiting></Root>My code is as follows , but it is not working accordingly kindly help me :SELECT 1 AS Tag, NULL AS Parent, 'B000' AS 'QueryWaiting!1!version' , NULL AS 'Authentication!2!id' , NULL AS 'Authentication!2!' Union ALL SELECT 2 as tag, 1 as parent, NULL, ControlStationID, [Password] FROM SW_ControlStations FOR XML EXPLICIT ,ROOT('Root')rams |
|
|
Ehan
Starting Member
19 Posts |
Posted - 2011-09-28 : 08:10:06
|
| Unless you insist on using EXPLICIT, you can get the same xml with this..SELECT 'B000' AS 'QueryWaiting/@version' , ControlStationID AS 'QueryWaiting/Authentication/@id', [Password] 'QueryWaiting/Authentication'FROM SW_ControlStations FOR XML PATH(''),ROOT('Root')Perosnally I find the using EXPLICIT is cumbersome and harder to maintain and so far managed to get around without having to use it :-) |
 |
|
|
jimoomba
Yak Posting Veteran
90 Posts |
Posted - 2011-09-28 : 08:19:16
|
| Thanks a lot Ehan!! It worked for me like a miracle. You saved me really :)rams |
|
|
|
Ehan
Starting Member
19 Posts |
Posted - 2011-09-28 : 08:21:20
|
| welcome! |
|
|
|
|
|
|
|
|
using XML EXPLICIT