Solved

Modify SQL Server 2005 Query to Produce XML in a Specific Format

Posted on 2008-10-27
5
569 Views
Last Modified: 2012-05-05
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>
0
Comment
Question by:JeffEdmunds
  • 3
  • 2
5 Comments
 
LVL 3

Expert Comment

by:Blackninja2007
ID: 22811961
your inner loop last line :

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


need to be

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

does it not ?

0
 
LVL 1

Author Comment

by:JeffEdmunds
ID: 22812018
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...
0
 
LVL 1

Author Comment

by:JeffEdmunds
ID: 22812044
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...
0
 
LVL 3

Accepted Solution

by:
Blackninja2007 earned 500 total points
ID: 22812096
try

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

that works on mine
0
 
LVL 1

Author Closing Comment

by:JeffEdmunds
ID: 31510271
That ... is ... PERFECT!
Thanks!!
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Customizing XML with Powershell 2 31
Need a modeling tool 2 39
Extracting nodes  using classical ASP 3 23
Replication failure 1 19
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question