JeffEdmunds
asked on
Modify SQL Server 2005 Query to Produce XML in a Specific Format
I have a temporary table in SQL Server 2005 which contains all the data I need to produce an XML-formatted file which will later be sent to our document services group for generating letters. I have a SQL script which selects data from this table, using the FOR XML clause to produce the results formatted as XML. It produces well-formatted XML, but not in the format that our document services group needs it in.
This is the code I am using currently:
-- Create final XML export file from ##Letters_Out temp table.
SELECT letter.[RECIPIENT_ID] AS [recipientID],
letter.[FIRST_NAME] AS [firstName],
letter.[MID_INIT] AS [middleInitial],
letter.[LAST_NAME] AS [lastName],
letter.[ADDRESS] AS [addressLine1],
letter.[ADDRESS2] AS [addressLine2],
letter.[CITY] AS [city],
letter.[STATE_CODE] AS [state],
letter.[ZIP_CODE] AS [zip],
(
SELECT Claim_Number AS 'claimNumber',
Alternate_Claim_Number AS 'alternateClaimNumber',
LTRIM(RTRIM(CONVERT(char, Start_Date, 101))) AS 'startDate',
LTRIM(RTRIM(CONVERT(char, Process_Date, 101))) AS 'processDate',
CAST(Claim_Amount AS Decimal(20,2)) AS 'claimAmount',
Exclusion_Code AS 'exclusionCode',
Exclusion_Description AS 'exclusionDescription',
YearID AS 'yearID'
FROM ##Letters_Out AS claim
WHERE claim.RECIPIENT_ID = letter.RECIPIENT_ID
FOR XML AUTO, TYPE, ROOT('claimDetails')
)
FROM ##Letters_Out AS [letter]
GROUP BY letter.[RECIPIENT_ID],
letter.[FIRST_NAME],
letter.[MID_INIT],
letter.[LAST_NAME],
letter.[ADDRESS],
letter.[ADDRESS2],
letter.[CITY],
letter.[STATE_CODE],
letter.[ZIP_CODE]
FOR XML AUTO, ELEMENTS, ROOT('denialLetters')
This is an example of the output it is producing currently:
<denialLetters>
<letter>
<recipientID>123-12-1234</ recipientI D>
<firstName>JOHN</firstName >
<middleInitial>M</middleIn itial>
<lastName>SMITH</lastName>
<addressLine1>123 ANYWHERE STREET</addressLine1>
<city>NORTH GRAFTON</city>
<state>MA</state>
<zip>01536</zip>
<claimDetails>
<claim claimNumber="11111111" alternateClaimNumber="2222 22226" startDate="08/05/2008" processDate="10/19/2008" claimAmount="50.00" exclusionCode="D06" exclusionDescription="PRIO R TO EFFECTIVE DATE" yearID="2008" />
<claim claimNumber="11111112" alternateClaimNumber="2222 22227" startDate="08/05/2008" processDate="10/19/2008" claimAmount="21.45" exclusionCode="D06" exclusionDescription="PRIO R TO EFFECTIVE DATE" yearID="2008" />
</claimDetails>
</letter>
</denialLetters>
What I am hoping someone could help with is modifying my FOR XML query in such a way that the data is formatted like this instead:
<denialLetters>
<letter>
<recipientID>123-12-1234</ recipientI D>
<firstName>JOHN</firstName >
<middleInitial>M</middleIn itial>
<lastName>SMITH</lastName>
<addressLine1>123 ANYWHERE STREET</addressLine1>
<city>NORTH GRAFTON</city>
<state>MA</state>
<zip>01536</zip>
<claimDetails>
<claim>
<claimNumber>11111111</cla imNumber>
<alternateClaimNumber>2222 22226</alt ernateClai mNumber>
<startDate>08/05/2008</sta rtDate>
<processDate>10/19/2008</p rocessDate >
<claimAmount>50.00</claimA mount>
<exclusionCode>D06</exclus ionCode>
<exclusionDescription>PRIO R TO EFFECTIVE DATE</exclusionDescription >
<yearID>2008</yearID>
</claim>
<claim>
<claimNumber>11111112</cla imNumber>
<alternateClaimNumber>2222 22227</alt ernateClai mNumber>
<startDate>08/05/2008</sta rtDate>
<processDate>10/19/2008</p rocessDate >
<claimAmount>21.45</claimA mount>
<exclusionCode>D06</exclus ionCode>
<exclusionDescription>PRIO R TO EFFECTIVE DATE</exclusionDescription >
<yearID>2008</yearID>
</claim>
</claimDetails>
</letter>
</denialLetters>
This is the code I am using currently:
-- Create final XML export file from ##Letters_Out temp table.
SELECT letter.[RECIPIENT_ID] AS [recipientID],
letter.[FIRST_NAME] AS [firstName],
letter.[MID_INIT] AS [middleInitial],
letter.[LAST_NAME] AS [lastName],
letter.[ADDRESS] AS [addressLine1],
letter.[ADDRESS2] AS [addressLine2],
letter.[CITY] AS [city],
letter.[STATE_CODE] AS [state],
letter.[ZIP_CODE] AS [zip],
(
SELECT Claim_Number AS 'claimNumber',
Alternate_Claim_Number AS 'alternateClaimNumber',
LTRIM(RTRIM(CONVERT(char, Start_Date, 101))) AS 'startDate',
LTRIM(RTRIM(CONVERT(char, Process_Date, 101))) AS 'processDate',
CAST(Claim_Amount AS Decimal(20,2)) AS 'claimAmount',
Exclusion_Code AS 'exclusionCode',
Exclusion_Description AS 'exclusionDescription',
YearID AS 'yearID'
FROM ##Letters_Out AS claim
WHERE claim.RECIPIENT_ID = letter.RECIPIENT_ID
FOR XML AUTO, TYPE, ROOT('claimDetails')
)
FROM ##Letters_Out AS [letter]
GROUP BY letter.[RECIPIENT_ID],
letter.[FIRST_NAME],
letter.[MID_INIT],
letter.[LAST_NAME],
letter.[ADDRESS],
letter.[ADDRESS2],
letter.[CITY],
letter.[STATE_CODE],
letter.[ZIP_CODE]
FOR XML AUTO, ELEMENTS, ROOT('denialLetters')
This is an example of the output it is producing currently:
<denialLetters>
<letter>
<recipientID>123-12-1234</
<firstName>JOHN</firstName
<middleInitial>M</middleIn
<lastName>SMITH</lastName>
<addressLine1>123 ANYWHERE STREET</addressLine1>
<city>NORTH GRAFTON</city>
<state>MA</state>
<zip>01536</zip>
<claimDetails>
<claim claimNumber="11111111" alternateClaimNumber="2222
<claim claimNumber="11111112" alternateClaimNumber="2222
</claimDetails>
</letter>
</denialLetters>
What I am hoping someone could help with is modifying my FOR XML query in such a way that the data is formatted like this instead:
<denialLetters>
<letter>
<recipientID>123-12-1234</
<firstName>JOHN</firstName
<middleInitial>M</middleIn
<lastName>SMITH</lastName>
<addressLine1>123 ANYWHERE STREET</addressLine1>
<city>NORTH GRAFTON</city>
<state>MA</state>
<zip>01536</zip>
<claimDetails>
<claim>
<claimNumber>11111111</cla
<alternateClaimNumber>2222
<startDate>08/05/2008</sta
<processDate>10/19/2008</p
<claimAmount>50.00</claimA
<exclusionCode>D06</exclus
<exclusionDescription>PRIO
<yearID>2008</yearID>
</claim>
<claim>
<claimNumber>11111112</cla
<alternateClaimNumber>2222
<startDate>08/05/2008</sta
<processDate>10/19/2008</p
<claimAmount>21.45</claimA
<exclusionCode>D06</exclus
<exclusionDescription>PRIO
<yearID>2008</yearID>
</claim>
</claimDetails>
</letter>
</denialLetters>
ASKER
Hello Blackninja2007,
Thank you for your quick response!
Unfortunately, when I change "TYPE" to "ELEMENTS" in that section, the variables are displayed, rather than the actual claim data, and they are grouped as a single line (at the end of the ZIP code element) without proper tags.
The line it produces looks like this:
<zip>01536</zip><claimDeta ils><claim ><claimNum ber> ... etc ... etc...
Thank you for your quick response!
Unfortunately, when I change "TYPE" to "ELEMENTS" in that section, the variables are displayed, rather than the actual claim data, and they are grouped as a single line (at the end of the ZIP code element) without proper tags.
The line it produces looks like this:
<zip>01536</zip><claimDeta
ASKER
hmm... sorry, the bulletin board converted the special characters... In the above line, every greater-than or less-than symbol is displayed as "&" (ampersand) "gt" or "&" "lt" with a semi-colon separator...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That ... is ... PERFECT!
Thanks!!
Thanks!!
FOR XML AUTO, TYPE, ROOT('claimDetails')
need to be
FOR XML AUTO, ELEMENTS, ROOT('claimDetails')
does it not ?