July 10, 2015 at 8:57 am
Our code that writes XML output is very hard to read.
It is using FOR XML PATH('XXX-XXX'), TYPE ) method.
Below I tried to present how our SQL code looks like (model).
I removed all the details. It's kind of a model.
I'm more concerned about code structure...
My question is.
With this current solution it is very hard to navigate through the code, to quickly find a specific place where specific XML element is being generated. Especially I hate these nested selects. Sometimes it's 3 levels nested:
SELECT
( SELECT...
( SELECT ...
I simplified the code a lot so that you can understand the structure. Real code is so much messier with lots of JOINS,CASE, etc.
Is there a better way to write SQL so that it's more readable?
Full version of stored procedure is attached.
Thanks,
################################################################
SELECT
(
SELECT
'<?xml version="1.0" encoding="ISO-8859-1"?>' AS T
) AS XML_DATA
UNION ALL
SELECT
(
SELECT
'<MessageModel xsi:schemaLocation="urn:schemas.td.com/ENT/MessageModel/2013/09/19_GDT GDT_MessageModel.xsd" xmlns="urn:schemas.td.com/ENT/MessageModel/2013/09/19_GDT" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">' AS T
) AS XML_DATA
UNION ALL
SELECT
(
SELECT
@V_PROVIDERID AS ProviderID
,@V_EFFECTIVEDT AS EffectiveDT
,@V_PREVIOUSEFFECTIVEDT AS PreviousEffectiveDT
,@V_REQUESTTS AS RequestTS
,@V_PARTY_COUNT AS PartyCount
,@V_ARRANGEMENT_COUNT AS ArrangementCount
,@V_APPMETADATASTRING AS AppMetaDataString
FOR XML PATH(''), ROOT('MessageHeader'), ELEMENTS
) AS XML_DATA
SELECT
(
SELECT
'<MessageContent>' AS T
) AS XML_DATA
UNION ALL
--------------------
------ Party -------
--------------------
SELECT
(
SELECT
Party.A,
Party.B,
Party.C,
Party.D
,
-----------------------------
------ Identification -------
-----------------------------
(
SELECT
Identification.X,
Identification.Y,
Identification.Z
FROM [FATCA_TDS].PartyIdentification AS Identification
WHERE bla-bla
FOR XML PATH('Identification'), TYPE
)
,
-----------------------------------
------ Party Classification -------
-----------------------------------
(
SELECT
PartyClassification.A,
PartyClassification.B,
PartyClassification.C
FROM [FATCA_TDS].PartyClassification AS PartyClassification
WHERE bla-bla
FOR XML PATH('PartyClassification'), TYPE
)
FROM FatcParty AS Party
--------------END-------------
WHERE
Party.ID = Parties.ID
AND ISNULL(Party.RECORD_STATUS, 0) = 0
FOR XML PATH ('Party'), ELEMENTS
) AS XML_DATA
FROM [FATCA_TDS].Party AS Parties
WHERE Parties.SOURCE_CODE = COALESCE( NULLIF(@P_SOURCE_CODE, 'ALL'), Parties.SOURCE_CODE)
UNION ALL
SELECT
(
SELECT
'</MessageContent>' AS T
) AS XML_DATA
UNION ALL
SELECT
(
SELECT
'</MessageModel>' AS T
) AS XML_DATA
;
July 17, 2015 at 1:28 pm
The short answer is NO!
The long answer is that the easiest way to get the appropriate structure in your XML documents using T-SQL is by using nested queries. Using this approach, it is also easy to set up your nested queries so that they correspond quite well to the structure of your XML documents.
It is possible to get XML documents with the correct structure without using subqueries. The RAW and AUTO options allow you to use queries that are relatively simple to read, but they give you limited control over the format of your XML document. The EXPLICIT option gives you tons of control, but is a major pain to set up correctly, and is very difficult to read because of all the extraneous calculations necessary to set it up to give you the correct output.
The best way to approach understanding queries that produce XML documents is to comment out all of the subqueries, executing the resulting query, and looking at the resulting XML document. Then uncomment another section and repeat.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply