Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Subject: XML from SQL table (for xml explicit or namespaces?) Expand / Collapse
Author
Message
Posted Wednesday, January 16, 2013 9:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:54 AM
Points: 10, Visits: 90
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;

Post #1407919
Posted Wednesday, January 16, 2013 9:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #1407934
Posted Wednesday, January 16, 2013 9:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:54 AM
Points: 10, Visits: 90
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
Post #1407959
Posted Wednesday, January 16, 2013 10:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:54 AM
Points: 10, Visits: 90
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;
Post #1407971
Posted Wednesday, January 16, 2013 10:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #1407979
Posted Thursday, January 17, 2013 2:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:54 AM
Points: 10, Visits: 90
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;

Post #1408222
Posted Thursday, January 17, 2013 2:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:54 AM
Points: 10, Visits: 90
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

Post #1408241
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse