Link to home
Start Free TrialLog in
Avatar of JeffEdmunds
JeffEdmundsFlag for United States of America

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</recipientID>
    <firstName>JOHN</firstName>
    <middleInitial>M</middleInitial>
    <lastName>SMITH</lastName>
    <addressLine1>123 ANYWHERE STREET</addressLine1>
    <city>NORTH GRAFTON</city>
    <state>MA</state>
    <zip>01536</zip>
    <claimDetails>
      <claim claimNumber="11111111" alternateClaimNumber="222222226" startDate="08/05/2008" processDate="10/19/2008" claimAmount="50.00" exclusionCode="D06" exclusionDescription="PRIOR TO EFFECTIVE DATE" yearID="2008" />
      <claim claimNumber="11111112" alternateClaimNumber="222222227" startDate="08/05/2008" processDate="10/19/2008" claimAmount="21.45" exclusionCode="D06" exclusionDescription="PRIOR 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</recipientID>
    <firstName>JOHN</firstName>
    <middleInitial>M</middleInitial>
    <lastName>SMITH</lastName>
    <addressLine1>123 ANYWHERE STREET</addressLine1>
    <city>NORTH GRAFTON</city>
    <state>MA</state>
    <zip>01536</zip>
    <claimDetails>
      <claim>
        <claimNumber>11111111</claimNumber>
        <alternateClaimNumber>222222226</alternateClaimNumber>
        <startDate>08/05/2008</startDate>
        <processDate>10/19/2008</processDate>
        <claimAmount>50.00</claimAmount>
        <exclusionCode>D06</exclusionCode>
        <exclusionDescription>PRIOR TO EFFECTIVE DATE</exclusionDescription>
        <yearID>2008</yearID>
      </claim>
      <claim>
        <claimNumber>11111112</claimNumber>
        <alternateClaimNumber>222222227</alternateClaimNumber>
        <startDate>08/05/2008</startDate>
        <processDate>10/19/2008</processDate>
        <claimAmount>21.45</claimAmount>
        <exclusionCode>D06</exclusionCode>
        <exclusionDescription>PRIOR TO EFFECTIVE DATE</exclusionDescription>
        <yearID>2008</yearID>
      </claim>
    </claimDetails>
  </letter>
</denialLetters>
Avatar of Blackninja2007
Blackninja2007

your inner loop last line :

            FOR XML AUTO, TYPE, ROOT('claimDetails')


need to be

            FOR XML AUTO, ELEMENTS, ROOT('claimDetails')

does it not ?

Avatar of JeffEdmunds

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><claimDetails><claim><claimNumber> ... etc ... etc...
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
Avatar of Blackninja2007
Blackninja2007

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That ... is ... PERFECT!
Thanks!!