January 16, 2013 at 9:21 am
Hi Guys
I’ve got bit of a problem with transforming SQL-table (SQL Server 2008 R2) based data into XML .
XML needs to be in particular format.
I’ve tried few methods but none of them transformed required data into correct format.
I’ve tried FOR XML including raw, auto, path, and explicit modes.
Each time I’ve tried something new I was very close to required solution in terms of XML layout. Unfortunately in this case it must be spot on match rather than close to.
I’m probably missing a simple trick here. Not sure how else I could solve it. Perhaps with the use of namespaces and schemas? If so can you give some advice please? I’m not really sure how to apply xsd schema in SQL environment.
Important in this case is the fact that PCC_AdmissionCharacteristics is at the same node level as PCC_CareActivityGroup. In addition all criticalCareActivityCodes should be grouped if there is more than one per activity data.
At the moment I’m getting:
<PCC_CareActivityGroup>
<ActivityDate>2012-05-27</ActivityDate>
<CriticalCareActivityCode>51</CriticalCareActivityCode>
</PCC_CareActivityGroup>
<PCC_CareActivityGroup>
<ActivityDate>2012-05-27</ActivityDate>
<CriticalCareActivityCode>60</CriticalCareActivityCode>
</PCC_CareActivityGroup>
What I should get is:
<PCC_CareActivityGroup>
<ActivityDate>2012-05-27</ActivityDate>
<CriticalCareActivityCode>51</CriticalCareActivityCode>
<CriticalCareActivityCode>60</CriticalCareActivityCode>
</PCC_CareActivityGroup>
That’s because Activity codes: 51 & 60 both fall on the same date.
Required data set returned from SQL table should be:
<ns:PaediatricCriticalCarePeriod PaediatricCriticalCareStructure="04">
<ns:PCC_AdmissionCharacteristics>
<ns:CriticalCareLocalIdentifier>1111111</ns:CriticalCareLocalIdentifier>
<ns:CriticalCareStartDate>2012-10-29</ns:CriticalCareStartDate>
</ns:PCC_AdmissionCharacteristics>
<ns:PCC_CareActivityGroup>
<ns:ActivityDate_CriticalCare>2012-10-29</ns:ActivityDate_CriticalCare>
<ns:CriticalCareActivityCode>11</ns:CriticalCareActivityCode>
<ns:CriticalCareActivityCode>22</ns:CriticalCareActivityCode>
<ns:CriticalCareActivityCode>33</ns:CriticalCareActivityCode>
</ns:PCC_CareActivityGroup>
<ns:PCC_CareActivityGroup>
<ns:ActivityDate_CriticalCare>2012-11-01</ns:ActivityDate_CriticalCare>
<ns:CriticalCareActivityCode>11</ns:CriticalCareActivityCode>
<ns:CriticalCareActivityCode>22</ns:CriticalCareActivityCode>
<ns:CriticalCareActivityCode>66</ns:CriticalCareActivityCode>
</ns:PCC_CareActivityGroup>
</ns:PaediatricCriticalCarePeriod>
<ns:PaediatricCriticalCarePeriod PaediatricCriticalCareStructure="04">
<ns:PCC_AdmissionCharacteristics>
<ns:CriticalCareLocalIdentifier>2222222</ns:CriticalCareLocalIdentifier>
<ns:CriticalCareStartDate>2012-10-30</ns:CriticalCareStartDate>
</ns:PCC_AdmissionCharacteristics>
<ns:PCC_CareActivityGroup>
<ns:ActivityDate_CriticalCare>2012-10-29</ns:ActivityDate_CriticalCare>
<ns:CriticalCareActivityCode>99</ns:CriticalCareActivityCode>
<ns:CriticalCareActivityCode>88</ns:CriticalCareActivityCode>
<ns:CriticalCareActivityCode>77</ns:CriticalCareActivityCode>
</ns:PCC_CareActivityGroup>
<ns:PCC_CareActivityGroup>
<ns:ActivityDate_CriticalCare>2012-11-01</ns:ActivityDate_CriticalCare>
<ns:CriticalCareActivityCode>99</ns:CriticalCareActivityCode>
<ns:CriticalCareActivityCode>88</ns:CriticalCareActivityCode>
<ns:CriticalCareActivityCode>11</ns:CriticalCareActivityCode>
</ns:PCC_CareActivityGroup>
</ns:PaediatricCriticalCarePeriod>
Required dataset should be as follows:
-One PaediatricCriticalCarePeriod can have one (1:1) PCC_AdmissionCharacteristics
-One PaediatricCriticalCarePeriod can have many (1:M) PCC_CareActivityGroup
-One PCC_CareActivityGroup can have one (1:1) ActivityDate_CriticalCare but there can be 1 or more CriticalCareActivityCodes
I have pasted sample PCC_CareActivityGroup code and I would really appreciate your expertise and help.
Thanks in advance
Mike
Sample code:
/*
create table #picu
(
EpisodeId int,
CriticalCareLocalIdentifier varchar(10),
CriticalCareStartDate varchar(10)
)
create table #Activities
(
CriticalCareLocalIdentifier varchar(10),
ActivityDate varchar(10),
CriticalCareActivityCode varchar(2)
)
insert into #picu values (6497895,'185719','2012-05-27')
insert into #picu values (6497895,'185719','2012-05-27')
insert into #picu values (6497895,'185719','2012-05-27')
insert into #picu values (6497895,'185719','2012-05-27')
insert into #picu values (6497895,'185719','2012-05-27')
insert into #picu values (6497895,'185719','2012-05-27')
insert into #picu values (6497895,'185719','2012-05-27')
insert into #picu values (6582674,'215364','2012-10-30')
insert into #picu values (6582674,'215364','2012-10-30')
insert into #picu values (6582674,'215364','2012-10-30')
insert into #picu values (6582674,'215693','2012-10-30')
insert into #picu values (6582674,'215693','2012-10-30')
insert into #Activities values ('185719','2012-05-27','50')
insert into #Activities values ('185719','2012-05-27','51')
insert into #Activities values ('185719','2012-05-27','60')
insert into #Activities values ('185719','2012-05-27','73')
insert into #Activities values ('185719','2012-05-28','50')
insert into #Activities values ('185719','2012-05-28','51')
insert into #Activities values ('185719','2012-05-28','57')
insert into #Activities values ('185719','2012-05-28','60')
insert into #Activities values ('185719','2012-05-28','73')
insert into #Activities values ('185719','2012-05-29','09')
insert into #Activities values ('185719','2012-05-29','50')
insert into #Activities values ('185719','2012-05-29','58')
insert into #Activities values ('185719','2012-05-29','73')
insert into #Activities values ('185719','2012-05-30','09')
insert into #Activities values ('185719','2012-05-30','73')
insert into #Activities values ('215364','2012-10-30','50')
insert into #Activities values ('215364','2012-10-30','52')
insert into #Activities values ('215364','2012-10-30','73')
insert into #Activities values ('215693','2012-10-30','52')
insert into #Activities values ('215693','2012-10-30','73')
select * from #picu
select * from #Activities
*/
-- for xml explicit solution:
select distinct
1 as Tag,
null as Parent,
'04' as [PaediatricCriticalCarePeriod!1!PaediatricCriticalCareStructure],
null as [PCC_AdmissionCharacteristics!2!CriticalCareLocalIdentifier!ELEMENT],
null as [PCC_AdmissionCharacteristics!2!CriticalCareStartDate!ELEMENT],
null as [PCC_CareActivityGroup!3!ActivityDate!ELEMENT],
null as [PCC_CareActivityGroup!3!CriticalCareActivityCode!ELEMENT]
from
#picu CriticalCarePeriod
inner join #Activities Activity
on CriticalCarePeriod.CriticalCareLocalIdentifier = Activity.CriticalCareLocalIdentifier
union all
select distinct
2 as Tag,
1 as Parent,
'04',
CriticalCarePeriod.CriticalCareLocalIdentifier,
CriticalCarePeriod.CriticalCareStartDate,
null as [PCC_CareActivityGroup!3!ActivityDate!ELEMENT],
null as [PCC_CareActivityGroup!3!CriticalCareActivityCode!ELEMENT]
from
#picu CriticalCarePeriod
inner join #Activities Activity
on CriticalCarePeriod.CriticalCareLocalIdentifier = Activity.CriticalCareLocalIdentifier
union all
select distinct
3 as Tag,
1 as Parent,
'04',
CriticalCarePeriod.CriticalCareLocalIdentifier,
CriticalCarePeriod.CriticalCareStartDate,
Activity.ActivityDate,
Activity.CriticalCareActivityCode
from
#picu CriticalCarePeriod
inner join #Activities Activity
on CriticalCarePeriod.CriticalCareLocalIdentifier = Activity.CriticalCareLocalIdentifier
order by
[PCC_AdmissionCharacteristics!2!CriticalCareLocalIdentifier!ELEMENT],
[PCC_AdmissionCharacteristics!2!CriticalCareStartDate!ELEMENT],
[PCC_CareActivityGroup!3!ActivityDate!ELEMENT]
for xml explicit;
January 16, 2013 at 9:32 am
I'm not entirely clear on the relationships between the two tables, but I can see that what you need to do is pivot the data before you convert it to XML.
You need a column for each CriticalCareActivityCode per day, instead of a row per code. That's a pivot operation. Details on how to do that are here: http://msdn.microsoft.com/en-us/library/ms177410(SQL.105).aspx
You need a row per date, and a column per code. I'm not sure how many codes you can have per day. If that's variable, you may need to do a "dynamic pivot". There are many articles online about how to do that. I like the one on Simple-Talk: http://www.simple-talk.com/sql/t-sql-programming/crosstab-pivot-table-workbench/. That's what I like, but it may or may not be what you need, so don't limit yourself to that article just because I recommend it.
- 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 16, 2013 at 9:55 am
Thanks for you reply.
Ok. lets say that I've got dynamic pivot what do I do next?
Do I have to create dynamic sql to include all CriticalCareAcivityCodes?
Have a look at new output from #picu table.
Any chance of getting bit of code back?
Thanks
Mike
create table #picu
(
EpisodeId int,
CriticalCareLocalIdentifier varchar(10),
CriticalCareStartDate varchar(10),
ActivityDate varchar(10),
CriticalCareActivityCode1 varchar(2),
CriticalCareActivityCode2 varchar(2),
CriticalCareActivityCode3 varchar(2),
CriticalCareActivityCode4 varchar(2),
CriticalCareActivityCode5 varchar(2),
CriticalCareActivityCode6 varchar(2)
)
insert into #picu values (6497895,'185719','2012-05-27','2012-05-28','11','22','33','44','55','66')
insert into #picu values (6582674,'215364','2012-10-30','2012-11-01','77','88','99','11',null,null)
insert into #picu values (6582674,'215364','2012-10-30','2012-11-02','99','11',null,null,null,null)
select * from #picu
January 16, 2013 at 10:01 am
Think I got in now (see the code below).
It's going to be painful to build dynamic pivot / SQL for the purpose of this task.
Any other solutions anyone?
Mike
select distinct
1 as Tag,
null as Parent,
'04' as [PaediatricCriticalCarePeriod!1!PaediatricCriticalCareStructure],
null as [PCC_AdmissionCharacteristics!2!CriticalCareLocalIdentifier!ELEMENT],
null as [PCC_AdmissionCharacteristics!2!CriticalCareStartDate!ELEMENT],
null as [PCC_CareActivityGroup!3!ActivityDate!ELEMENT],
null as [PCC_CareActivityGroup!3!CriticalCareActivityCode1!ELEMENT],
null as [PCC_CareActivityGroup!3!CriticalCareActivityCode2!ELEMENT],
null as [PCC_CareActivityGroup!3!CriticalCareActivityCode3!ELEMENT],
null as [PCC_CareActivityGroup!3!CriticalCareActivityCode4!ELEMENT],
null as [PCC_CareActivityGroup!3!CriticalCareActivityCode5!ELEMENT],
null as [PCC_CareActivityGroup!3!CriticalCareActivityCode6!ELEMENT]
from
#picu CriticalCarePeriod
union all
select distinct
2 as Tag,
1 as Parent,
'04',
CriticalCarePeriod.CriticalCareLocalIdentifier,
CriticalCarePeriod.CriticalCareStartDate,
null as [PCC_CareActivityGroup!3!ActivityDate!ELEMENT],
null as [PCC_CareActivityGroup!3!CriticalCareActivityCode1!ELEMENT],
null as [PCC_CareActivityGroup!3!CriticalCareActivityCode2!ELEMENT],
null as [PCC_CareActivityGroup!3!CriticalCareActivityCode3!ELEMENT],
null as [PCC_CareActivityGroup!3!CriticalCareActivityCode4!ELEMENT],
null as [PCC_CareActivityGroup!3!CriticalCareActivityCode5!ELEMENT],
null as [PCC_CareActivityGroup!3!CriticalCareActivityCode6!ELEMENT]
from
#picu CriticalCarePeriod
union all
select distinct
3 as Tag,
1 as Parent,
'04',
CriticalCarePeriod.CriticalCareLocalIdentifier,
CriticalCarePeriod.CriticalCareStartDate,
CriticalCarePeriod.ActivityDate,
CriticalCarePeriod.CriticalCareActivityCode1,
CriticalCarePeriod.CriticalCareActivityCode2,
CriticalCarePeriod.CriticalCareActivityCode3,
CriticalCarePeriod.CriticalCareActivityCode4,
CriticalCarePeriod.CriticalCareActivityCode5,
CriticalCarePeriod.CriticalCareActivityCode6
from
#picu CriticalCarePeriod
order by
[PCC_AdmissionCharacteristics!2!CriticalCareLocalIdentifier!ELEMENT],
[PCC_AdmissionCharacteristics!2!CriticalCareStartDate!ELEMENT],
[PCC_CareActivityGroup!3!ActivityDate!ELEMENT]
for xml explicit;
January 16, 2013 at 10:08 am
Does this do what you basically need?
IF OBJECT_ID(N'tempdb..#picu') IS NOT NULL
DROP TABLE #picu;
CREATE TABLE #picu
(EpisodeId INT,
CriticalCareLocalIdentifier VARCHAR(10),
CriticalCareStartDate VARCHAR(10),
ActivityDate VARCHAR(10),
CriticalCareActivityCode1 VARCHAR(2),
CriticalCareActivityCode2 VARCHAR(2),
CriticalCareActivityCode3 VARCHAR(2),
CriticalCareActivityCode4 VARCHAR(2),
CriticalCareActivityCode5 VARCHAR(2),
CriticalCareActivityCode6 VARCHAR(2))
INSERT INTO #picu
VALUES (6497895, '185719', '2012-05-27', '2012-05-28', '11', '22', '33', '44', '55', '66')
INSERT INTO #picu
VALUES (6582674, '215364', '2012-10-30', '2012-11-01', '77', '88', '99', '11', NULL, NULL)
INSERT INTO #picu
VALUES (6582674, '215364', '2012-10-30', '2012-11-02', '99', '11', NULL, NULL, NULL, NULL)
SELECT #picu.EpisodeId,
#picu.CriticalCareLocalIdentifier,
#picu.CriticalCareStartDate,
#picu.ActivityDate,
#picu.CriticalCareActivityCode1 AS CriticalCareActivityCode,
'',
#picu.CriticalCareActivityCode2 AS CriticalCareActivityCode,
'',
#picu.CriticalCareActivityCode3 AS CriticalCareActivityCode,
'',
#picu.CriticalCareActivityCode4 AS CriticalCareActivityCode,
'',
#picu.CriticalCareActivityCode5 AS CriticalCareActivityCode,
'',
#picu.CriticalCareActivityCode6 AS CriticalCareActivityCode
FROM #picu
FOR XML PATH('PCC_CareActivityGroup'),
TYPE;
- 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 17, 2013 at 2:04 am
Thanks again.
Nearly there. Still got a problem to put together in one group: ActivityDate & CriticalCareActivityCode
See the code below.
Mike
SELECT
'04' as "@PaediatricCriticalCareStructure",
p.CriticalCareLocalIdentifier as "PCC_AdmissionCharacteristics/CriticalCareLocalIdentifier",
p.CriticalCareStartDate as "PCC_AdmissionCharacteristics/CriticalCareStartDate",
p.ActivityDate as "PCC_CareActivityGroup/ActivityDate",
'',
p.CriticalCareActivityCode1 as "PCC_CareActivityGroup/CriticalCareActivityCode",
'',
p.CriticalCareActivityCode2 as "PCC_CareActivityGroup/CriticalCareActivityCode",
'',
p.CriticalCareActivityCode3 as "PCC_CareActivityGroup/CriticalCareActivityCode",
'',
p.CriticalCareActivityCode4 as "PCC_CareActivityGroup/CriticalCareActivityCode",
'',
p.CriticalCareActivityCode5 as "PCC_CareActivityGroup/CriticalCareActivityCode",
'',
p.CriticalCareActivityCode6 as "PCC_CareActivityGroup/CriticalCareActivityCode"
FROM #picu p
FOR XML PATH('PaediatricCriticalCarePeriod'),
TYPE;
January 17, 2013 at 2:36 am
I got it. Nested XML that's the right answer.
Thanks for all your help.
Final solution:
select
'04' as "@PaediatricCriticalCareStructure",
p.CriticalCareLocalIdentifier,
p.CriticalCareStartDate,
(select
p.ActivityDate,
'',
p.CriticalCareActivityCode1 as CriticalCareActivityCode,
'',
p.CriticalCareActivityCode2 as CriticalCareActivityCode,
'',
p.CriticalCareActivityCode3 as CriticalCareActivityCode,
'',
p.CriticalCareActivityCode4 as CriticalCareActivityCode,
'',
p.CriticalCareActivityCode5 as CriticalCareActivityCode,
'',
p.CriticalCareActivityCode6 as CriticalCareActivityCode
from
#picu p1
where
p.CriticalCareLocalIdentifier = p1.CriticalCareLocalIdentifier
for
xml path('PCC_CareActivityGroup'),
type,
elements)
from
#picu p
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply