Building a Large XML file to Include or Exclude Groups and Detail According to Parametrised Info

  • Hello all,

    I am trying to create an xml file for our customers, based on information stored in our database. The idea is that the customer will be able to request different levels of detail, depending on their needs and we will be able to keep configuration information against that customer to drive the automation of the production of the xml as they requested, without having to rewrite the production scripts with every new customer.

    I have made a simplified mock-up of the data that we would be sending out. The real thing is larger and more complex - the table #ImportedeValues below, that is the source in this case, would actually hold a large number of parent groups and a query would have to be performed to find which to include for each file.

    A couple of example parameters that would be used to define the level of detail included in the XML in the scripts shown below would be @IncludeGroupLevelSummary BIT and @IncludeTransactionDetails BIT.

    Here is what part of the xml file and building script might look like, with the highest amount of detail included (for instance, it might be that @IncludeGroupLevelSummary = 1 and @IncludeTransactionDetails BIT = 1):

    CREATE TABLE #ImportedValues

    ( ParentGroupId INT

    ,ParentGroupName VARCHAR(MAX)

    ,GroupId INT

    ,GroupName VARCHAR(MAX)

    ,TransactionId INT

    ,RecoveryTotal MONEY

    ,PaymentTotal MONEY

    ,TransactionDate DATETIME

    )

    INSERT #ImportedValues

    SELECT 1, 'Claim',1,'Personal',10,NULL,20.01,'2014-01-01 00:00:02'UNION

    SELECT 1, 'Claim',1,'Personal',11,-10,NULL,'2014-02-15 13:42:56'UNION

    SELECT 1, 'Claim',2,'Medical',12,NULL,10.5,'2014-02-15 14:01:23'UNION

    SELECT 1, 'Claim',2,'Medical',13,NULL,30,'2014-03-10 09:14:12'

    SELECT * FROM #ImportedValues

    -------------------------------------------------------------------------------------------------------------------------

    SELECT DISTINCT

    1AS Tag

    ,NULLAS Parent

    ,ParentGroupIdAS [ParentGroup!1!ParentGroupId!ELEMENT]

    ,NULLAS [FinancialSummary!2!PaymentTotal!ELEMENT]

    ,NULLAS [FinancialSummary!2!RecoveryTotal!ELEMENT]

    ,NULLAS [Groups!3]

    ,NULLAS [Group!4!Name!ELEMENT]

    ,NULLAS [FinancialSummary!5!PaymentTotal!ELEMENT]

    ,NULLAS [FinancialSummary!5!RecoveryTotal!ELEMENT]

    ,NULLAS [Transactions!6]

    ,NULLAS [Transaction!7!Payment!ELEMENT]

    ,NULLAS [Transaction!7!Recovery!ELEMENT]

    ,NULLAS [Transaction!7!CreateDate!ELEMENT]

    FROM #ImportedValues

    UNION ALL

    SELECT

    2AS Tag

    ,1AS Parent

    ,ParentGroupId

    ,SUM(ISNULL(PaymentTotal,0))

    ,SUM(ISNULL(RecoveryTotal,0))

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    FROM #ImportedValuesAS ParentLevelSummary

    GROUP BY ParentGroupId

    UNION ALL

    SELECT DISTINCT

    3AS Tag

    ,1AS Parent

    ,ParentGroupId

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    FROM #ImportedValuesAS Groups

    UNION ALL

    SELECT DISTINCT

    4AS Tag

    ,3AS Parent

    ,ParentGroupId

    ,NULL

    ,NULL

    ,NULL

    ,GroupName

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    FROM #ImportedValuesAS Group_

    UNION ALL

    SELECT DISTINCT

    5AS Tag

    ,4AS Parent

    ,ParentGroupId

    ,NULL

    ,NULL

    ,NULL

    ,GroupName

    ,SUM(ISNULL(PaymentTotal,0))

    ,SUM(ISNULL(RecoveryTotal,0))

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    FROM #ImportedValuesAS GroupLevelSummary

    GROUP BY GroupId,ParentGroupId,GroupName

    UNION ALL

    SELECT DISTINCT

    6AS Tag

    ,5AS Parent

    ,ParentGroupId

    ,NULL

    ,NULL

    ,NULL

    ,GroupName

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    FROM #ImportedValuesAS Transactions

    UNION ALL

    SELECT DISTINCT

    7AS Tag

    ,6AS Parent

    ,ParentGroupId

    ,NULL

    ,NULL

    ,NULL

    ,GroupName

    ,NULL

    ,NULL

    ,NULL

    ,PaymentTotal

    ,NULL

    ,TransactionDate

    FROM #ImportedValuesAS Transaction1

    WHERE PaymentTotal IS NOT NULL

    UNION ALL

    SELECT DISTINCT

    7AS Tag

    ,6AS Parent

    ,ParentGroupId

    ,NULL

    ,NULL

    ,NULL

    ,GroupName

    ,NULL

    ,NULL

    ,NULL

    ,PaymentTotal

    ,RecoveryTotal

    ,TransactionDate

    FROM #ImportedValuesAS Transaction2

    WHERE RecoveryTotal IS NOT NULL

    ORDER BY [ParentGroup!1!ParentGroupId!ELEMENT],[Group!4!Name!ELEMENT],[Transaction!7!CreateDate!ELEMENT]

    FOR XML EXPLICIT, ROOT('ParentGroups')

    DROP TABLE #ImportedValues

    this gives the following output:

    <ParentGroups>

    <ParentGroup>

    <ParentGroupId>1</ParentGroupId>

    <FinancialSummary>

    <PaymentTotal>60.5100</PaymentTotal>

    <RecoveryTotal>-10.0000</RecoveryTotal>

    </FinancialSummary>

    <Groups>

    <Group>

    <Name>Medical</Name>

    <FinancialSummary>

    <PaymentTotal>40.5000</PaymentTotal>

    <RecoveryTotal>0.0000</RecoveryTotal>

    <Transactions>

    <Transaction>

    <Payment>10.5000</Payment>

    <CreateDate>2014-02-15T14:01:23</CreateDate>

    </Transaction>

    <Transaction>

    <Payment>30.0000</Payment>

    <CreateDate>2014-03-10T09:14:12</CreateDate>

    </Transaction>

    </Transactions>

    </FinancialSummary>

    </Group>

    <Group>

    <Name>Personal</Name>

    <FinancialSummary>

    <PaymentTotal>20.0100</PaymentTotal>

    <RecoveryTotal>-10.0000</RecoveryTotal>

    <Transactions>

    <Transaction>

    <Payment>20.0100</Payment>

    <CreateDate>2014-01-01T00:00:02</CreateDate>

    </Transaction>

    <Transaction>

    <Recovery>-10.0000</Recovery>

    <CreateDate>2014-02-15T13:42:56</CreateDate>

    </Transaction>

    </Transactions>

    </FinancialSummary>

    </Group>

    </Groups>

    </ParentGroup>

    </ParentGroups>

    Now, if I wish to remove some detail, I can do that easily enough from the bottom up by deleting parts of the FOR XML part of the script, to give something at the other end of the scale (for instance, it might be that @IncludeGroupLevelSummary = 0 and @IncludeTransactionDetails BIT = 0:

    CREATE TABLE #ImportedValues

    ( ParentGroupId INT

    ,ParentGroupName VARCHAR(MAX)

    ,GroupId INT

    ,GroupName VARCHAR(MAX)

    ,TransactionId INT

    ,RecoveryTotal MONEY

    ,PaymentTotal MONEY

    ,TransactionDate DATETIME

    )

    INSERT #ImportedValues

    SELECT 1, 'Claim',1,'Personal',10,NULL,20.01,'2014-01-01 00:00:02'UNION

    SELECT 1, 'Claim',1,'Personal',11,-10,NULL,'2014-02-15 13:42:56'UNION

    SELECT 1, 'Claim',2,'Medical',12,NULL,10.5,'2014-02-15 14:01:23'UNION

    SELECT 1, 'Claim',2,'Medical',13,NULL,30,'2014-03-10 09:14:12'

    SELECT * FROM #ImportedValues

    -------------------------------------------------------------------------------------------------------------------------

    SELECT DISTINCT

    1AS Tag

    ,NULLAS Parent

    ,ParentGroupIdAS [ParentGroup!1!ParentGroupId!ELEMENT]

    ,NULLAS [FinancialSummary!2!PaymentTotal!ELEMENT]

    ,NULLAS [FinancialSummary!2!RecoveryTotal!ELEMENT]

    ,NULLAS [Groups!3]

    ,NULLAS [Group!4!Name!ELEMENT]

    ,NULLAS [FinancialSummary!5!PaymentTotal!ELEMENT]

    ,NULLAS [FinancialSummary!5!RecoveryTotal!ELEMENT]

    ,NULLAS [Transactions!6]

    ,NULLAS [Transaction!7!Payment!ELEMENT]

    ,NULLAS [Transaction!7!Recovery!ELEMENT]

    ,NULLAS [Transaction!7!CreateDate!ELEMENT]

    FROM #ImportedValues

    UNION ALL

    SELECT

    2AS Tag

    ,1AS Parent

    ,ParentGroupId

    ,SUM(ISNULL(PaymentTotal,0))

    ,SUM(ISNULL(RecoveryTotal,0))

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    ,NULL

    FROM #ImportedValuesAS ParentLevelSummary

    GROUP BY ParentGroupId

    ORDER BY [ParentGroup!1!ParentGroupId!ELEMENT],[Group!4!Name!ELEMENT],[Transaction!7!CreateDate!ELEMENT]

    FOR XML EXPLICIT, ROOT('ParentGroups')

    DROP TABLE #ImportedValues

    which gives this output:

    <ParentGroups>

    <ParentGroup>

    <ParentGroupId>1</ParentGroupId>

    <FinancialSummary>

    <PaymentTotal>60.5100</PaymentTotal>

    <RecoveryTotal>-10.0000</RecoveryTotal>

    </FinancialSummary>

    </ParentGroup>

    </ParentGroups>

    My problem is that I do not want to have to write out hundreds of different queries in order to cover every possible level of detail, nor do I really want to have a huge set of Ifs in a single behemoth of a query. I would much rather be able to build the xml into the right level of detail using a building blocks approach, where each block can be created in a distinct script or procedure and then included or excluded by one controlling piece of SQL (even if this uses a large IF, at least each condition would only contain a small EXEC statement or something). This would make future auditing and editing much easier to achieve.

    I have seen that it is possible to build xml out of smaller xml chunks, but I cannot see how to do that in this case, splicing the different levels of XML into the right place to build up the larger file; I'm not even sure that is the best way to go or what I would need to do to achieve this.

    Any help would be much appreciated; I'm at a bit of a dead end

  • I have found a solution to the problem - in each union-ed select statement, I can add a WHERE clause to include or exclude the results. If the select statement in question returns no results, then it does not add to the structure of the resultant xml. So, if there is an @IncludeGroupSummary BIT parameter, I would put "WHERE @IncludeGroupSummary = 1" after the relevant selects statement to only include the summary when required. the FOR EXPLICIT copes admirably well.

  • I like XML FOR EXPLICIT, it's really powerful and allows that finer grain of control when constructing XML. However from SQL 2005 onwards, the other FOR XML options ( eg RAW, PATH, AUTO ) are undeniably simpler to construct and maintain.

    Here's the equivalent FOR XML PATH statement for your FOR XML EXPLICIT example. This also supports the WHERE clause option you've described but would be much easier to extend if your xml structure changes:

    DECLARE

    @IncludeGroupSummary BIT = 1,

    @IncludeFinancialSummary BIT = 1,

    @IncludeTransactionSummary BIT = 1

    SELECT

    pg.ParentGroupId AS ParentGroupId,

    (

    SELECT

    SUM(ISNULL(PaymentTotal,0)) AS PaymentTotal,

    SUM(ISNULL(RecoveryTotal,0)) AS RecoveryTotal

    FROM #ImportedValues fs

    WHERE pg.ParentGroupId = fs.ParentGroupId

    AND @IncludeFinancialSummary = 1

    FOR XML PATH(''), TYPE

    ) AS FinancialSummary,

    (

    SELECT

    GroupName AS Name,

    (

    SELECT

    SUM( ISNULL( PaymentTotal, 0 ) ) AS PaymentTotal,

    SUM( ISNULL( RecoveryTotal, 0 ) ) AS RecoveryTotal,

    (

    SELECT

    PaymentTotal AS Payment,

    RecoveryTotal AS Recovery,

    TransactionDate AS CreateDate

    FROM #ImportedValues t

    WHERE g.ParentGroupId = t.ParentGroupId

    AND g.GroupId = t.GroupId

    AND @IncludeTransactionSummary = 1

    FOR XML PATH('Transaction'), TYPE

    ) AS Transactions

    FROM #ImportedValues fs

    WHERE g.ParentGroupId = g.ParentGroupId

    AND g.GroupId = fs.GroupId

    FOR XML PATH(''), TYPE

    ) AS FinancialSummary

    FROM (

    SELECT DISTINCT ParentGroupId, GroupId, GroupName

    FROM #ImportedValues g

    WHERE pg.ParentGroupId = g.ParentGroupId

    AND @IncludeGroupSummary = 1

    ) g

    ORDER BY GroupName

    FOR XML PATH('Group'), TYPE

    ) AS Groups

    FROM

    ( SELECT DISTINCT ParentGroupId FROM #ImportedValues ) pg

    FOR XML PATH('ParentGroup'), ROOT('ParentGroups'), TYPE

    HTH

  • Thanks wBob - that equivalent by a different method will be a really useful tool for me; I am much less familiar with the FOR XML PATH method and will go through your example in detail.

    I really appreciate the time you have taken here and will heed you advice that following this route will make for an easier life in the future!

    Cheers,

    Mark

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply