Post reply

Xml format issue

  • October 4, 2011 at 12:44 am

    #244769

    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

    #1390330

    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.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • October 4, 2011 at 11:37 pm

    #1390500

    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