October 3, 2014 at 3:35 pm
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 ?
October 3, 2014 at 10:05 pm
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>
October 4, 2014 at 8:27 am
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
October 4, 2014 at 2:03 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy