locked
Generate records as XML file format in SQL Server RRS feed

  • Question

  • Hi All

    My table format has mentioned below :

    SalesID,DeliveryDate,OrderDate,StoreNumber,ItemID
    1,23/04/2013,23/06/2013,2,45
    1,23/04/2013,23/06/2013,2,45
    2,23/04/2013,23/06/2013,2,45
    2,23/04/2013,23/06/2013,2,45


    How can i generate XML file as mentioned below from above table :


    <AST>
     <AS SalesID=1 DeliveryDate=23/04/2013>
       <ST>
        <OrderDate =23/06/2013 StoreNumber=2 ItemId=45>
        <OrderDate =23/06/2013 StoreNumber=2 ItemId=45>
       <ST>
     </AS>
     <AS SalesID=2 DeliveryDate=23/04/2013>
      <ST>
        <OrderDate =23/06/2013 StoreNumber=2 ItemId=45>
        <OrderDate =23/06/2013 StoreNumber=2 ItemId=45>
      <ST>
    </AS>
    </AST>

    Thursday, July 25, 2013 11:48 AM

Answers

  • Hi vittalaranga,

    I think it is better to add OrderDate,StoreNumber,ItemID attributes to “<ST> </ST>” tag rather than putting the attributes as following with no tag:

    <OrderDate =23/06/2013 StoreNumber=2 ItemId=45>

    You can refer to the following codes:

    declare @SaleTable table
    (
    	SalesID int,
    	DeliveryDate date,
    	OrderDate date,
    	StoreNumber int,
    	ItemID int
    )
    
    insert into @SaleTable (SalesID,DeliveryDate,OrderDate,StoreNumber,ItemID) values (1,'04/23/2013','06/23/2013',2,45),(1,'04/23/2013','06/23/2013',2,45),(
    2,'04/23/2013','06/23/2013',2,45),(
    2,'04/23/2013','06/23/2013',2,45)
    
    SELECT
        tag,
        Parent,
        [AST!1!],
    	[AS!2!SalesID],
    	 [AS!2!DeliveryDate],
    	[ST!3!OrderDate],
    	[ST!3!StoreNumber],
    	[ST!3!ItemId]
    from(
    SELECT
        1 AS tag,
        NULL AS Parent,
    	0 AS Sort,
        NULL AS 'AST!1!',
    	NULL AS 'AS!2!SalesID',
    	NULL AS 'AS!2!DeliveryDate',
    	NULL as 'ST!3!OrderDate',
    	NULL as 'ST!3!StoreNumber',
    	NULL as 'ST!3!ItemId'
    union all
    SELECT
    	2 AS tag,
        1 AS Parent,
    	SalesID*100 as Sort,
        NULL,
    	SalesID,
    	DeliveryDate ,
    	NULL,
    	NULL,
    	NULL
    	from @SaleTable
    	group by SalesID,DeliveryDate
    Union all
    SELECT
    	3 AS tag,
        2 AS Parent,
    	SalesID*100+2 as Sort,
        NULL,
    	NULL,
    	NULL,
    	OrderDate,
    	StoreNumber,
    	ItemId
    	from @SaleTable
    ) a order by Sort
    FOR XML EXPLICIT
    
    /*
    <AST>
      <AS SalesID="1" DeliveryDate="2013-04-23">
        <ST OrderDate="2013-06-23" StoreNumber="2" ItemId="45" />
        <ST OrderDate="2013-06-23" StoreNumber="2" ItemId="45" />
      </AS>
      <AS SalesID="2" DeliveryDate="2013-04-23">
        <ST OrderDate="2013-06-23" StoreNumber="2" ItemId="45" />
        <ST OrderDate="2013-06-23" StoreNumber="2" ItemId="45" />
      </AS>
    </AST>
    */
    
    You can also refer to the example on the following link:

    FOR XML EXPLICIT Tutorial- Part 1
    http://beyondrelational.com/modules/2/blogs/28/posts/10344/for-xml-explicit-tutorial-part-1.aspx


    Allen Li
    TechNet Community Support

    Monday, July 29, 2013 9:20 AM

All replies

  • Read this

    http://blog.sqlauthority.com/2009/02/12/sql-server-simple-example-of-creating-xml-file-using-t-sql/ 

    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by Prajesh Thursday, July 25, 2013 12:21 PM better
    • Proposed as answer by Prajesh Thursday, July 25, 2013 12:23 PM
    Thursday, July 25, 2013 12:21 PM
  • Hi vittalaranga,

    I think it is better to add OrderDate,StoreNumber,ItemID attributes to “<ST> </ST>” tag rather than putting the attributes as following with no tag:

    <OrderDate =23/06/2013 StoreNumber=2 ItemId=45>

    You can refer to the following codes:

    declare @SaleTable table
    (
    	SalesID int,
    	DeliveryDate date,
    	OrderDate date,
    	StoreNumber int,
    	ItemID int
    )
    
    insert into @SaleTable (SalesID,DeliveryDate,OrderDate,StoreNumber,ItemID) values (1,'04/23/2013','06/23/2013',2,45),(1,'04/23/2013','06/23/2013',2,45),(
    2,'04/23/2013','06/23/2013',2,45),(
    2,'04/23/2013','06/23/2013',2,45)
    
    SELECT
        tag,
        Parent,
        [AST!1!],
    	[AS!2!SalesID],
    	 [AS!2!DeliveryDate],
    	[ST!3!OrderDate],
    	[ST!3!StoreNumber],
    	[ST!3!ItemId]
    from(
    SELECT
        1 AS tag,
        NULL AS Parent,
    	0 AS Sort,
        NULL AS 'AST!1!',
    	NULL AS 'AS!2!SalesID',
    	NULL AS 'AS!2!DeliveryDate',
    	NULL as 'ST!3!OrderDate',
    	NULL as 'ST!3!StoreNumber',
    	NULL as 'ST!3!ItemId'
    union all
    SELECT
    	2 AS tag,
        1 AS Parent,
    	SalesID*100 as Sort,
        NULL,
    	SalesID,
    	DeliveryDate ,
    	NULL,
    	NULL,
    	NULL
    	from @SaleTable
    	group by SalesID,DeliveryDate
    Union all
    SELECT
    	3 AS tag,
        2 AS Parent,
    	SalesID*100+2 as Sort,
        NULL,
    	NULL,
    	NULL,
    	OrderDate,
    	StoreNumber,
    	ItemId
    	from @SaleTable
    ) a order by Sort
    FOR XML EXPLICIT
    
    /*
    <AST>
      <AS SalesID="1" DeliveryDate="2013-04-23">
        <ST OrderDate="2013-06-23" StoreNumber="2" ItemId="45" />
        <ST OrderDate="2013-06-23" StoreNumber="2" ItemId="45" />
      </AS>
      <AS SalesID="2" DeliveryDate="2013-04-23">
        <ST OrderDate="2013-06-23" StoreNumber="2" ItemId="45" />
        <ST OrderDate="2013-06-23" StoreNumber="2" ItemId="45" />
      </AS>
    </AST>
    */
    
    You can also refer to the example on the following link:

    FOR XML EXPLICIT Tutorial- Part 1
    http://beyondrelational.com/modules/2/blogs/28/posts/10344/for-xml-explicit-tutorial-part-1.aspx


    Allen Li
    TechNet Community Support

    Monday, July 29, 2013 9:20 AM