Solved

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

Posted on 2008-10-27
5
576 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Create a Windows 10 custom Image with custom task bar and custom start menu using XML for deployment.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

624 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