Create XML file from AS400 stored procedure returning multiple datasets

• vinaygambhir

  SSC Veteran

  Points: 251

  More actions

  October 3, 2014 at 3:35 pm

  #397456

  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 ?

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  October 3, 2014 at 10:05 pm

  #1750117

  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>

• vinaygambhir

  SSC Veteran

  Points: 251

  More actions

  October 4, 2014 at 8:27 am

  #1750137

  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

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  October 4, 2014 at 2:03 pm

  #1750152

  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>