CODEWITHSUNDEEP
sql-serverxmlt-sqlpath
salvationishere
salvationishere

Reputation: 3511

How to get results in a specific XML format from a TSQL query?

I have the following T-SQL query that I want to convert into XML file. I was trying to use FOR XML Path, but this isn't working the way I need it to. Here is my T-SQL table definition:

create table TN_DataFeed
(
--Patient uniqueidentifier,
ProviderPatientNo varchar(50) null, 
LastName varchar(25), 
FirstName varchar(25), 
SSN char(9) null, 
DOB char(10) null,
Gender tinyint null, 
Race tinyint null, 
Ethnicity tinyint null, 
--PhoneAssessment varchar(50), 
ProviderPhoneAssessmentID varchar(50), 
CallEndDate char(10), 
CallEndTime varchar(8)
)

My data matches this format above and successfully is inserted. But I need my XML to look like:

<Patient>
  <ProviderPatientNo>ProviderPatientNo0</ProviderPatientNo>
  <LastName>LastName0</LastName>
  <FirstName>FirstName0</FirstName>
  <SSN>000000000</SSN>
  <DOB>2006-05-04</DOB>
  <Gender>1</Gender>
  <Race>1</Race>
  <Ethnicity>1</Ethnicity>
  <PhoneAssessment>
    <ProviderPhoneAssessmentId>52854541</ProviderPhoneAssessmentId>
    <CallEndDate>2006-05-04</CallEndDate>
    <CallEndTime>01:01:01.001</CallEndTime>
  </PhoneAssessment>
</Patient>

This is my XML Path code for retrieving the above T-SQL query:

select 
ProviderPatientNo,
LastName,FirstName,SSN,DOB,Gender,Race,Ethnicity,
(
    select distinct
    ProviderPhoneAssessmentId, CallEndDate, CallEndTime
    from TN_DataFeed
For XML path ('PhoneAssessment'), root('PhoneAssessment2'), type
)
from TN_DataFeed
For XML path ('Patient'), root('Patient_root'), type

Note that I have not yet included all of the columns. Instead, I'm just trying to get the Patient section working. Notice how in the example XML file below, the section that shows how Patient is the parent node of ProviderPatientNo, LastName, FirstName, SSN, DOB, Gender, Race, and Ethnicity.

But instead, my XML output from my above XML Path is:

<Patient_root>
  <Patient>
    <ProviderPatientNo>00200543</ProviderPatientNo>
    <LastName>Ga</LastName>
    <FirstName>Ti</FirstName>
    <SSN>4108</SSN>
    <DOB>1998-08-16</DOB>
    <Gender>2</Gender>
    <Race>2</Race>
    <Ethnicity>3</Ethnicity>
    <PhoneAssessment2>
      <PhoneAssessment>
        <ProviderPhoneAssessmentId>BEA5487B-82E9-4226-B883-BFBFE7EF2B1A</ProviderPhoneAssessmentId>
        <CallEndDate>2013-09-16</CallEndDate>
        <CallEndTime>22:00:00</CallEndTime>
      </PhoneAssessment>
      <PhoneAssessment>
        <ProviderPhoneAssessmentId>C8F39E2F-BC4A-48AD-BD07-C07EB8384AD7</ProviderPhoneAssessmentId>
        <CallEndDate>2013-09-16</CallEndDate>
        <CallEndTime>16:24:00</CallEndTime>
      </PhoneAssessment>
    </PhoneAssessment2>
  </Patient>
  <Patient>
    <ProviderPatientNo>00200543</ProviderPatientNo>
    <LastName>Ga</LastName>
    <FirstName>Ti</FirstName>
    <SSN>4108</SSN>
    <DOB>1998-08-16</DOB>
    <Gender>2</Gender>
    <Race>2</Race>
    <Ethnicity>3</Ethnicity>
    <PhoneAssessment2>
      <PhoneAssessment>
        <ProviderPhoneAssessmentId>BEA5487B-82E9-4226-B883-BFBFE7EF2B1A</ProviderPhoneAssessmentId>
        <CallEndDate>2013-09-16</CallEndDate>
        <CallEndTime>22:00:00</CallEndTime>
      </PhoneAssessment>
      <PhoneAssessment>
        <ProviderPhoneAssessmentId>C8F39E2F-BC4A-48AD-BD07-C07EB8384AD7</ProviderPhoneAssessmentId>
        <CallEndDate>2013-09-16</CallEndDate>
        <CallEndTime>16:24:00</CallEndTime>
      </PhoneAssessment>
    </PhoneAssessment2>
  </Patient>

So the problems are:

Many of the elements are repeated. I tried using Distinct to limit repetitions, but this caused the error:

The xml data type cannot be selected as DISTINCT because it is not comparable.

Upvotes: 1

Views: 584

Answers (1)

Preet Sangha
Preet Sangha

Reputation: 65546

Ok: First thing I notice is that you're querying the information_schema not the actual table, and all this gives you is the table schema metadata. Can you change your query to query the table please? SELECT ... FROM TN_DataFeed.

See this example : http://technet.microsoft.com/en-us/library/bb510462.aspx

Something like this should get you close to what you want: 

SELECT ProviderPatientNo,
       LastName,
       FirstName,
       SSN,
       DOB,
       Gender,
       Race,
       PhoneAssessment ....

FROM TN_DataFeed
FOR XML PATH ('Patient');

you can replace the distinct with a GROUP BY

select 
      ProviderPhoneAssessmentId, CallEndDate, CallEndTime
from 
      TN_DataFeed
group by 
      ProviderPhoneAssessmentId, CallEndDate, CallEndTime

Upvotes: 4

Related Questions