none
how to handle 2 <Transaction> with this SP

    Question

  • below the 2 different XML that implies I have one or more than one <Transaction> under single <Header>,

    XML - 1 

    DECLARE @StrXML XML
    SET @StrXML = '<PDI_Approval_Message>
     <Header>
     <SourceSystem Value="PDI/Workforce" Name="WF"></SourceSystem>
     </Header>
     <Transactions>
    
     <Transaction>
     <Attributes>
     <WF_Regular_Hrs>12</WF_Regular_Hrs>
     <WF_Overtime_Hrs>3</WF_Overtime_Hrs>
     </Attributes>
     </Transaction>
    
     </Transactions>
    </PDI_Approval_Message>'
    EXEC test @StrXML 
    

    XML - 2 

    DECLARE @StrXML XML
    SET @StrXML = '<PDI_Approval_Message>
     <Header>
     <SourceSystem Value="PDI/Workforce" Name="WF"></SourceSystem>
     </Header>
     <Transactions>
    
     <Transaction>
     <Attributes>
     <WF_Regular_Hrs>12</WF_Regular_Hrs>
     <WF_Overtime_Hrs>3</WF_Overtime_Hrs>
     </Attributes>
     </Transaction>
    
     <Transaction>
     <Attributes>
     <WF_Regular_Hrs>18</WF_Regular_Hrs>
     <WF_Overtime_Hrs>4</WF_Overtime_Hrs>
     </Attributes>
     </Transaction>
    
     </Transactions>
    </PDI_Approval_Message>'
    EXEC test @StrXML 
    

    below SP return 1 row perfectly with XML - 1  and it won't retrieve 2 rows for XML - 2 , 

    output for XML - 1, 

    UID   SourceSystemName     SourceSystemValue    WF_Regular_Hrs     WF_Overtime_Hrs

     1             WF                            PDI/Workforce              12                        3

    I need below output for XML - 2, which is not coming by below SP, what need to do for this???

    output for XML - 1,

    UID   SourceSystemName     SourceSystemValue    WF_Regular_Hrs     WF_Overtime_Hrs

     1             WF                            PDI/Workforce              12                        3

     2             WF                            PDI/Workforce              18                        4

     

    and here is the SP,

    CREATE PROCEDURE test
    (	
    	@StrXML XML
    )
    AS
    Begin	
    	DECLARE @vTable TABLE (x XML)
    	INSERT INTO @vTable(X) SELECT @StrXML 
    
    	DECLARE @cols NVARCHAR(MAX),
    			@sql NVARCHAR(MAX)
    	SET @Cols = 
    	STUFF(
    	(
    	SELECT ', MAX(CASE WHEN [Attribute]=' + QUOTENAME(attributes.value('local-name(.)','VARCHAR(25)'),CHAR(39)) +
    	' THEN [Attribute_Value] ELSE NULL END) AS ' + QUOTENAME(attributes.value('local-name(.)','VARCHAR(25)')) + CHAR(13)
    	FROM @vTable t
    	CROSS APPLY @StrXML.nodes('/PDI_Approval_Message/Transactions/Transaction/Attributes/*')Attributes (attributes)
    	FOR XML PATH(''),TYPE
    	).value('.','nvarchar(max)'),1,1,'')
    
    	SET @sql = N'	
    	DECLARE @vTable TABLE (x XML)
    	INSERT INTO @vTable(X) SELECT @StrXML
    	
    	SELECT 
    		ROW_NUMBER() OVER( ORDER BY ( SELECT 1 ) ) AS UID,
    		SourceSystemName,
    		SourceSystemValue, ' + CHAR(13) + @cols + '
    	FROM(
    	SELECT 
    		header.value(''./SourceSystem[1]/@Name'',''VARCHAR(15)'') AS SourceSystemName,
    		header.value(''./SourceSystem[1]/@Value'',''VARCHAR(15)'') AS SourceSystemValue,
    		attributes.value(''local-name(.)'',''VARCHAR(25)'') Attribute,
    		attributes.value(''.'',''VARCHAR(15)'') AS Attribute_Value
    		FROM @vTable
    		CROSS APPLY x.nodes(''/PDI_Approval_Message/Header'')Header(header)
    		CROSS APPLY x.nodes(''/PDI_Approval_Message/Transactions/Transaction/Attributes/*'')Attributes (attributes)
    	) AS x
    	GROUP BY 
    		SourceSystemName,
    		SourceSystemValue'
    
    	DECLARE @parameters NVARCHAR(1000)
    	SET @parameters = '@StrXML as XML'
    	EXEC sp_executesql @sql, @parameters, @StrXML
    END
    GO
    
    
    Wednesday, August 25, 2010 6:26 AM

Answers

  • How about using OPENXML:

    ALTER PROCEDURE test
    @SQLXML XML
    AS
    DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @SQLXML
    
    SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [UID], *
    FROM    OPENXML (@idoc, '/PDI_Approval_Message/Transactions/Transaction/Attributes',2)
          WITH (SourceSystemValue varchar(100) '../../../Header/SourceSystem/@Value',
    					SourceSystemName varchar(100) '../../../Header/SourceSystem/@Name',
    					WF_Regular_Hrs int,
    					WF_Overtime_Hrs int)
    					
    EXEC sp_xml_removedocument @idoc
    GO
    

    every day is a school day
    • Marked as answer by Yazdani ISTS Tuesday, September 07, 2010 8:35 AM
    Wednesday, September 01, 2010 9:32 AM
    Moderator