October 4, 2011 at 12:44 am
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)
October 4, 2011 at 1:14 pm
Please provide some ready to use sample data as described in the first link in my signature together with your expected result based on the sample data provided.
October 4, 2011 at 11:37 pm
Hi,
Thanks a lot for your reply.I got the answer what i was looking. I did it with the help of my buddy.
Thanks dude:)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply