none
SQL to XML (Hierarchy)

    Question

  • Hi

    I've looked at the forum but i haven't found really a good solution. I have this order detail table that i want to export as XML.

    ID	Description GroupID	Pos	Amount
    1	Order_1		1	1	100
    2	Order_1		1	2	80
    3	Order_1		1	3	50
    4	Order_2		2	1	30
    5	Order_2		2	2	40

    And the output should look like this:

    <?xml version='1.0' encoding='UTF-8'?>
    <OrderContainer>
    	<TaskCount>1</TaskCount>
    	<Task>
    		<Parameter>
    			<Application>Order</Application>
    		</Parameter>
    	</Task>
    	<Order>
    		<ID>1</ID>
    		<Description>Order_1</Description>
    			<Positions>
    				<Position>
    					<ID>1</ID>
    					<Amount>100</Amount>
    				</Position>
    				<Position>
    					<ID>2</ID>
    					<Amount>80</Amount>
    				</Position>
    				<Position>
    					<ID>3</ID>
    					<Amount>50</Amount>
    				</Position>
    			</Positions>
    	</Order>
    	<Order>
    		<ID>1</ID>
    		<Description>Order_2</Description>
    			<Positions>
    				<Position>
    					<ID>1</ID>
    					<Amount>30</Amount>
    				</Position>
    				<Position>
    					<ID>2</ID>
    					<Amount>40</Amount>
    				</Position>
    			</Positions>
    	</Order>
    </OrderContainer>
    How can i accomplish this?


    Monday, April 29, 2013 3:31 PM

Answers

  • Hi,

    Try this..

    declare @tbl table(id int, description varchar(30), groupid  int, pos int, amount numeric(15,2))
    
    insert into @tbl values 
    
    
    (1,	'Order_1',1,1,100),
    (2	,	'Order_1',		1,	2,	80),
    (3	,	'Order_1',		1,	3,	50),
    (4	,	'Order_2',		2,	1,	30),
    (5	,	'Order_2',		2,	2,	40)
    
     BEGIN TRY  
      DECLARE @testXML XML
      
      SELECT @testXML =   
       (SELECT  '1' AS 'TaskCount'
         ,'Order' AS 'Task/Parameter/Application'     
         ,(  
           SELECT 
    				id AS 'Order/ID'			  
    			  ,description as 'Order/Description'
    			  ,pos as 'Order/Positions/Position/ID'
    			  ,amount as 'Order/Positions/Position/Amount'
          FROM @tbl
          
          FOR XML PATH('')  ,TYPE
          )    
               
         FOR XML PATH('OrderContainer')       
           )    
      
     
        
      SELECT @testXML  
      
      
        
     END TRY  
     BEGIN CATCH  	
    	SELECT ERROR_NUMBER() ErrorNumber, ERROR_MESSAGE() ErrorMessage FOR XML RAW  
     END CATCH  
     
     
    
    
    
    GO
    
    
    

    Monday, April 29, 2013 3:55 PM
  • Try this:

    DECLARE @table TABLE ( ID INT, Description VARCHAR(20), GroupId INT, pos INT, Amount INT )
    
    INSERT INTO @table VALUES
    	( 1, 'Order_1', 1, 1, 100 ),
    	( 2, 'Order_1', 1, 2, 80 ),
    	( 3, 'Order_1', 1, 3, 50 ),
    	( 4, 'Order_2', 2, 1, 30 ),
    	( 5, 'Order_2', 2, 2, 40 )
    
    SELECT
    	(
    	SELECT
    		1 AS "TaskCount",
    		'Order' AS "Task/Parameter/Application"
    	FOR XML PATH(''), TYPE
    	),
    	(
    	SELECT 
    		o.GroupId AS "ID",
    		o.[Description] AS "Description",
    		(
    		SELECT 
    			pos AS "ID",
    			Amount AS "Amount"
    		FROM @table p
    		WHERE o.GroupId = p.GroupId
    		  AND o.Description = p.Description
    		FOR XML PATH('Position'), TYPE
    		) AS "Positions"
    	FROM ( SELECT DISTINCT Description, GroupID FROM @table ) o
    	FOR XML PATH('Order'), TYPE
    	)
    FOR XML PATH('OrderContainer'), TYPE

    Monday, April 29, 2013 6:19 PM

All replies

  • Hi,

    Try this..

    declare @tbl table(id int, description varchar(30), groupid  int, pos int, amount numeric(15,2))
    
    insert into @tbl values 
    
    
    (1,	'Order_1',1,1,100),
    (2	,	'Order_1',		1,	2,	80),
    (3	,	'Order_1',		1,	3,	50),
    (4	,	'Order_2',		2,	1,	30),
    (5	,	'Order_2',		2,	2,	40)
    
     BEGIN TRY  
      DECLARE @testXML XML
      
      SELECT @testXML =   
       (SELECT  '1' AS 'TaskCount'
         ,'Order' AS 'Task/Parameter/Application'     
         ,(  
           SELECT 
    				id AS 'Order/ID'			  
    			  ,description as 'Order/Description'
    			  ,pos as 'Order/Positions/Position/ID'
    			  ,amount as 'Order/Positions/Position/Amount'
          FROM @tbl
          
          FOR XML PATH('')  ,TYPE
          )    
               
         FOR XML PATH('OrderContainer')       
           )    
      
     
        
      SELECT @testXML  
      
      
        
     END TRY  
     BEGIN CATCH  	
    	SELECT ERROR_NUMBER() ErrorNumber, ERROR_MESSAGE() ErrorMessage FOR XML RAW  
     END CATCH  
     
     
    
    
    
    GO
    
    
    

    Monday, April 29, 2013 3:55 PM
  • Try this:

    DECLARE @table TABLE ( ID INT, Description VARCHAR(20), GroupId INT, pos INT, Amount INT )
    
    INSERT INTO @table VALUES
    	( 1, 'Order_1', 1, 1, 100 ),
    	( 2, 'Order_1', 1, 2, 80 ),
    	( 3, 'Order_1', 1, 3, 50 ),
    	( 4, 'Order_2', 2, 1, 30 ),
    	( 5, 'Order_2', 2, 2, 40 )
    
    SELECT
    	(
    	SELECT
    		1 AS "TaskCount",
    		'Order' AS "Task/Parameter/Application"
    	FOR XML PATH(''), TYPE
    	),
    	(
    	SELECT 
    		o.GroupId AS "ID",
    		o.[Description] AS "Description",
    		(
    		SELECT 
    			pos AS "ID",
    			Amount AS "Amount"
    		FROM @table p
    		WHERE o.GroupId = p.GroupId
    		  AND o.Description = p.Description
    		FOR XML PATH('Position'), TYPE
    		) AS "Positions"
    	FROM ( SELECT DISTINCT Description, GroupID FROM @table ) o
    	FOR XML PATH('Order'), TYPE
    	)
    FOR XML PATH('OrderContainer'), TYPE

    Monday, April 29, 2013 6:19 PM