Create XML file from AS400 stored procedure returning multiple datasets

  • Hi..

    Here is the problem.

    I have a store procedure in MC400 which I can call from SSMS using the below command:

    EXEC ('CALL GETENROLLMENT() ')At serverName

    Now this command returns two data sets like:

    HA HB HC HD HE

    1112

    112571ABC14

    113574ABC16

    114577ABC87

    DADBDCDD

    1115566VG02

    1115566VG02

    1115566VG02

    I want to generate two different XML files from these two datasets.

    Is there any way this can be achieved in SSIS or t-sql ?

  • Best option would be to use T-SQL, it is simple and straight forward, here is a quick example.

    This sample uses XML AUTO, by changing to XML PATH, almost any structure can be produced.

    😎

    ;WITH SAMPLE_DATA(HA,HB,HC,HD,HE) AS

    (SELECT * FROM (VALUES

    (1,1,1,2,NULL )

    ,(1,1,2,571,'ABC14')

    ,(1,1,3,574,'ABC16')

    ,(1,1,4,577,'ABC87')

    ) AS X(HA,HB,HC,HD,HE)

    )

    SELECT

    *

    FROM SAMPLE_DATA SD

    FOR XML AUTO, ELEMENTS;

    Results

    <SD>

    <HA>1</HA>

    <HB>1</HB>

    <HC>1</HC>

    <HD>2</HD>

    </SD>

    <SD>

    <HA>1</HA>

    <HB>1</HB>

    <HC>2</HC>

    <HD>571</HD>

    <HE>ABC14</HE>

    </SD>

    <SD>

    <HA>1</HA>

    <HB>1</HB>

    <HC>3</HC>

    <HD>574</HD>

    <HE>ABC16</HE>

    </SD>

    <SD>

    <HA>1</HA>

    <HB>1</HB>

    <HC>4</HC>

    <HD>577</HD>

    <HE>ABC87</HE>

    </SD>

  • Thanks.. But the problem is I have to 1st use the below command:

    EXEC ('CALL GETENROLLMENT() ')At serverName which returns the two data sets.

    I do not know how to handle this command and store the data sets it returns in two tables and then generate the two XML files

  • vinaygambhir (10/4/2014)


    Thanks.. But the problem is I have to 1st use the below command:

    EXEC ('CALL GETENROLLMENT() ')At serverName which returns the two data sets.

    I do not know how to handle this command and store the data sets it returns in two tables and then generate the two XML files

    This is straight forward in the likes of C# but not in TSQL. Best bet is to add a dummy output column in the originating function so the number of column matches for all the result sets.

    😎

    Here is a quick sample, first a multiple result set stored procedure

    USE tempdb;

    GO

    ALTER PROCEDURE dbo.USP_TEST_MULTISET

    AS

    ;WITH SAMPLE_DATA_1(HA,HB,HC,HD,HE) AS

    (SELECT * FROM (VALUES

    (1,1,1,2,NULL )

    ,(1,1,2,571,'ABC14')

    ,(1,1,3,574,'ABC16')

    ,(1,1,4,577,'ABC87')

    ) AS X(HA,HB,HC,HD,HE)

    )

    SELECT * FROM SAMPLE_DATA_1

    ;WITH SAMPLE_DATA_2(DUMMY,DA,DB,DC,DD) AS

    (SELECT * FROM (VALUES

    (NULL,1,1,1,'5566VG02')

    ,(NULL,1,1,1,'5566VG02')

    ,(NULL,1,1,1,'5566VG02')

    ) AS X(DUMMY,DA,DB,DC,DD)

    )

    SELECT * FROM SAMPLE_DATA_2

    GO

    Capture the output

    USE tempdb;

    GO

    DECLARE @RESSET TABLE (C1 INT NULL, C2 INT NULL, C3 INT NULL, C4 INT NULL, C5 VARCHAR(12) NULL)

    INSERT INTO @RESSET

    EXEC dbo.USP_TEST_MULTISET

    SELECT * FROM @RESSET

    FOR XML PATH('RES'), ELEMENTS

    Results (XML)

    <RES>

    <C1>1</C1>

    <C2>1</C2>

    <C3>1</C3>

    <C4>2</C4>

    </RES>

    <RES>

    <C1>1</C1>

    <C2>1</C2>

    <C3>2</C3>

    <C4>571</C4>

    <C5>ABC14</C5>

    </RES>

    <RES>

    <C1>1</C1>

    <C2>1</C2>

    <C3>3</C3>

    <C4>574</C4>

    <C5>ABC16</C5>

    </RES>

    <RES>

    <C1>1</C1>

    <C2>1</C2>

    <C3>4</C3>

    <C4>577</C4>

    <C5>ABC87</C5>

    </RES>

    <RES>

    <C2>1</C2>

    <C3>1</C3>

    <C4>1</C4>

    <C5>5566VG02</C5>

    </RES>

    <RES>

    <C2>1</C2>

    <C3>1</C3>

    <C4>1</C4>

    <C5>5566VG02</C5>

    </RES>

    <RES>

    <C2>1</C2>

    <C3>1</C3>

    <C4>1</C4>

    <C5>5566VG02</C5>

    </RES>

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply