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-10-04 : 02:43:02
|
| Hi All,Iam having the following query using xml explicit (shown below) which displays an xml in the following manner:<RequestDelivery version="B000"> <Authentication id="57985539">NQIGUTXD</Authentication> <ReturnMessage id="3655370668"/> <Authentication id="57985540">FONFSNPX</Authentication> <ReturnMessage id="3655565794" /></RequestDelivery>But i want the xml in the following format : Based on Authentication id First time it should display as follows :<RequestDelivery version="B000"> <Authentication id="57985539">NQIGUTXD</Authentication> <ReturnMessage id="3655370668"/</RequestDelivery>Then for the second time it shold display with another Authentication id elements like :<RequestDelivery version="B000"> <Authentication id="57985540">FONFSNPX</Authentication> <ReturnMessage id="3655565794" /></RequestDelivery>Kindly see the query below and let me know the exact query for the same:DECLARE @MessageWaitingXml AS XML SELECT @MessageWaitingXml = (SELECT * from( SELECT 1 AS Tag, NULL AS Parent, 'B000' AS 'RequestDelivery!1!version' , NULL AS 'Authentication!2!id' , NULL AS 'Authentication!2!' , NULL AS 'ReturnMessage!3!id' UNION ALL SELECT --top 1 2 AS tag, 1 AS parent, NULL, ControlStationID, [Password], NULL FROM SW_ControlStations --where priority =1 UNION ALL SELECT --top (CAST(@Value AS INT)) 3 AS TAG, 1 AS PARENT, NUll, NULL, NULL, ReturnMessageID FROM SW_ReturnMessages WHERE STATUS=1)A FOR xml EXPLICIT) rams |
|
|
jimoomba
Yak Posting Veteran
90 Posts |
Posted - 2011-10-04 : 09:21:30
|
| Hi All,I got the answer for this and got solved.Thanks,Ramrams |
 |
|
|
|
|
|
|
|
Xml format in this manner