Solved

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

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

803 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