January 18, 2012 at 1:33 pm
Hello all, I'm very new to writing sql for xml output. I'm struggling with how to write the syntax for multiple values. I'm not very familiar with the terminology but I'll do my best to explain. I have a patient table that I need to join to several tables that have multiple values.
SELECT
P.PatientID AS "PatientInfo/PatientID",
P.PatientName AS "PatientInfo/PatientName",
P.UnitNumber AS "PatientInfo/UnitNumber",
PVS.DateTime AS "PatientInfo/patientVitalSign/vitalSignDateTime",
PVS.Vital AS "PatientInfo/patientVitalSign/vitalSign/vital",
PVS.Value AS "PatientInfo/patientVitalSign/vitalSign/value"
FROM Patient P
LEFT JOIN PatientVitalSigns PVS
ON P.PatientID = PVS.PatientID
FOR XML PATH;
The way it is written it will start the patient data over for each new row. I need the patient to only print once then the vital signs repeat as needed like below:
<- <patientVitalSign>
<vitalSignDateTime>2011-07-11T20:00:00</vitalSignDateTime>
- <vitalSign>
<vital>Temp</vital>
<value>98.2</value>
</vitalSign>
- <vitalSign>
<vital>Pulse</vital>
<value>142</value>
</vitalSign>
- <vitalSign>
<vital>Resp</vital>
<value>66</value>
</vitalSign>
</patientVitalSign>
- <patientVitalSign>
<vitalSignDateTime>2011-06-11T16:00:00</vitalSignDateTime>
- <vitalSign>
<vital>Temp</vital>
<value>98.5</value>
Thank you for the help!!! Jamie
January 18, 2012 at 1:48 pm
The usual way to do that is to nest XML queries instead of joining tables.
SELECT ColA, ColB,
(SELECT Col1, Col2
FROM dbo.MySubtable
WHERE MySubtable.MainTableID = MainTable.ID
FOR XML PATH('SubTable'), TYPE) AS SubTable
FROM dbo.MainTable
FOR XML PATH('MainTable'), TYPE;
That kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 19, 2012 at 10:24 am
Thank you so much! This works great! Jamie
January 19, 2012 at 10:54 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply