Subject: XML from SQL table (for xml explicit or namespaces?)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Subject: XML from SQL table (for xml explicit...

Subject: XML from SQL table (for xml explicit or namespaces?)

Rate Topic

Display Mode

Topic Options

Author

Message

michal.giczewski

Posted Wednesday, January 16, 2013 9:21 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 12, 2013 4:26 AM
Points: 7, Visits: 55

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

GSquared

Posted Wednesday, January 16, 2013 9:32 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:55 PM
Points: 15,442, Visits: 9,571

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

michal.giczewski

Posted Wednesday, January 16, 2013 9:55 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 12, 2013 4:26 AM
Points: 7, Visits: 55

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

michal.giczewski

Posted Wednesday, January 16, 2013 10:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 12, 2013 4:26 AM
Points: 7, Visits: 55

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

GSquared

Posted Wednesday, January 16, 2013 10:08 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:55 PM
Points: 15,442, Visits: 9,571

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

michal.giczewski

Posted Thursday, January 17, 2013 2:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 12, 2013 4:26 AM
Points: 7, Visits: 55

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

michal.giczewski

Posted Thursday, January 17, 2013 2:36 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 12, 2013 4:26 AM
Points: 7, Visits: 55

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 »

Permissions