July 28, 2014 at 8:49 am
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
July 29, 2014 at 4:06 am
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.
July 29, 2014 at 5:12 am
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
July 29, 2014 at 5:20 am
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