Solved

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

Posted on 2008-10-27
5
562 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Select2 jquery help 9 74
Pivot Table for a join with partition by and over clause 7 27
XML XSLT If loop 4 19
Sql query 107 22
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

930 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now