user3564110 user3564110 - 9 months ago 36
SQL Question

XSLT V1 - looping to create a table from XML

I have a requirement to create a report which shows occupancy of beds on a ward, for a date range specified by the user. The end result should look something like this:



.ritz .waffle a { color: inherit; }.ritz .waffle .s1{border-bottom:1px SOLID #000000;border-right:1px SOLID #000000;text-align:center;color:#000000;background-color:#d8d8d8;font-family:'Calibri',Arial;font-size:11pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:0px 3px 0px 3px;}.ritz .waffle .s2{border-bottom:1px SOLID #000000;border-right:1px SOLID #000000;text-align:right;color:#000000;background-color:#d8d8d8;font-family:'Calibri',Arial;font-size:11pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:0px 3px 0px 3px;}.ritz .waffle .s4{border-bottom:1px SOLID #000000;border-right:1px SOLID #000000;text-align:left;color:#000000;background-color:#d8d8d8;font-family:'Calibri',Arial;font-size:11pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:0px 3px 0px 3px;}.ritz .waffle .s3{border-bottom:1px SOLID #000000;border-right:1px SOLID #000000;text-align:left;color:#000000;background-color:#ffffff;font-family:'Calibri',Arial;font-size:11pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:0px 3px 0px 3px;}.ritz .waffle .s0{border-right:1px SOLID #000000;text-align:left;color:#000000;background-color:#ffffff;font-family:'Calibri',Arial;font-size:11pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:0px 3px 0px 3px;}.ritz .waffle .s5{border-bottom:1px SOLID #000000;border-right:1px SOLID #000000;text-align:center;color:#006100;background-color:#c6efce;font-family:'Calibri',Arial;font-size:11pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:0px 3px 0px 3px;}.ritz .waffle .s6{border-bottom:1px SOLID #000000;border-right:1px SOLID #000000;text-align:center;color:#9c6500;background-color:#ffeb9c;font-family:'Calibri',Arial;font-size:11pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:0px 3px 0px 3px;}

<div class="ritz grid-container" dir="ltr"><table class="waffle no-grid" cellspacing="0" cellpadding="0"><thead><tr><th class="row-header freezebar-origin-ltr"></th><th id="850396799C0" style="width:60px" class="column-headers-background">A</th><th id="850396799C1" style="width:60px" class="column-headers-background">B</th><th id="850396799C2" style="width:60px" class="column-headers-background">C</th><th id="850396799C3" style="width:60px" class="column-headers-background">D</th><th id="850396799C4" style="width:60px" class="column-headers-background">E</th><th id="850396799C5" style="width:60px" class="column-headers-background">F</th><th id="850396799C6" style="width:60px" class="column-headers-background">G</th><th id="850396799C7" style="width:60px" class="column-headers-background">H</th><th id="850396799C8" style="width:60px" class="column-headers-background">I</th><th id="850396799C9" style="width:60px" class="column-headers-background">J</th><th id="850396799C10" style="width:60px" class="column-headers-background">K</th><th id="850396799C11" style="width:60px" class="column-headers-background">L</th><th id="850396799C12" style="width:60px" class="column-headers-background">M</th></tr></thead><tbody><tr style='height:19px;'><th id="850396799R0" style="height: 19px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 19px;">1</div></th><td class="s0"></td><td class="s1" colspan="8">June</td><td class="s1" colspan="4">July</td></tr><tr style='height:19px;'><th id="850396799R1" style="height: 19px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 19px;">2</div></th><td class="s0"></td><td class="s2">23</td><td class="s2">24</td><td class="s2">25</td><td class="s2">26</td><td class="s2">27</td><td class="s2">28</td><td class="s2">29</td><td class="s2">30</td><td class="s2">1</td><td class="s2">2</td><td class="s2">3</td><td class="s2">4</td></tr><tr style='height:19px;'><th id="850396799R2" style="height: 19px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 19px;">3</div></th><td class="s3"></td><td class="s4">Mo</td><td class="s4">Tu</td><td class="s4">We</td><td class="s4">Th</td><td class="s4">Fr</td><td class="s4">Sa</td><td class="s4">SU</td><td class="s4">Mo</td><td class="s4">Tu</td><td class="s4">We</td><td class="s4">Th</td><td class="s4">Fr</td></tr><tr style='height:19px;'><th id="850396799R3" style="height: 19px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 19px;">4</div></th><td class="s4">Bed 1</td><td class="s5" colspan="9">Sam Smith</td><td class="s3"></td><td class="s3"></td><td class="s3"></td></tr><tr style='height:19px;'><th id="850396799R4" style="height: 19px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 19px;">5</div></th><td class="s4">Bed 2</td><td class="s6" colspan="11">Doris Grey (Planned</td><td class="s3"></td></tr><tr style='height:19px;'><th id="850396799R5" style="height: 19px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 19px;">6</div></th><td class="s4">Bed 3</td><td class="s3"></td><td class="s3"></td><td class="s3"></td><td class="s3"></td><td class="s3"></td><td class="s3"></td><td class="s5" colspan="6">Suri Patel</td></tr></tbody></table></div>





The constraint is that I have to build the result page using a single XML document which is dynamically created from a single SQL query.



ritz .waffle a {
color: inherit;
}
.ritz .waffle .s0 {
border-bottom: 1px SOLID #000000;
border-right: 1px SOLID #000000;
text-align: center;
color: #000000;
background-color: #d8d8d8;
font-family: 'Calibri', Arial;
font-size: 11pt;
vertical-align: bottom;
white-space: nowrap;
direction: ltr;
padding: 0px 3px 0px 3px;
}
.ritz .waffle .s2 {
border-left: none;
text-align: center;
color: #000000;
background-color: #d8d8d8;
font-family: 'Calibri', Arial;
font-size: 11pt;
vertical-align: bottom;
white-space: nowrap;
direction: ltr;
padding: 0px 3px 0px 3px;
}
.ritz .waffle .s6 {
border-bottom: 1px SOLID #000000;
border-right: 1px SOLID #000000;
}
.ritz .waffle .s7 {
border-bottom: 1px SOLID #000000;
border-right: 1px SOLID #000000;
text-align: left;
color: #000000;
background-color: #ffffff;
font-family: 'Calibri', Arial;
font-size: 11pt;
vertical-align: bottom;
white-space: nowrap;
direction: ltr;
padding: 0px 3px 0px 3px;
}
.ritz .waffle .s4 {
border-right: none;
text-align: center;
color: #000000;
background-color: #ffffff;
font-family: 'Calibri', Arial;
font-size: 11pt;
vertical-align: bottom;
white-space: nowrap;
direction: ltr;
padding: 0px 3px 0px 3px;
}
.ritz .waffle .s5 {
border-left: none;
text-align: center;
color: #000000;
background-color: #ffffff;
font-family: 'Calibri', Arial;
font-size: 11pt;
vertical-align: bottom;
white-space: nowrap;
direction: ltr;
padding: 0px 3px 0px 3px;
}
.ritz .waffle .s1 {
border-right: none;
text-align: center;
color: #000000;
background-color: #d8d8d8;
font-family: 'Calibri', Arial;
font-size: 11pt;
vertical-align: bottom;
white-space: nowrap;
direction: ltr;
padding: 0px 3px 0px 3px;
}
.ritz .waffle .s3 {
border-bottom: 1px SOLID #000000;
border-right: 1px SOLID #000000;
text-align: center;
color: #000000;
background-color: #ffffff;
font-family: 'Calibri', Arial;
font-size: 11pt;
vertical-align: bottom;
white-space: nowrap;
direction: ltr;
padding: 0px 3px 0px 3px;
}

<div class="ritz grid-container" dir="ltr">
<table class="waffle" cellspacing="0" cellpadding="0">
<thead>
<tr>
<th class="row-header freezebar-origin-ltr"></th>
<th id="2029419615C0" style="width:60px" class="column-headers-background">A</th>
<th id="2029419615C1" style="width:60px" class="column-headers-background">B</th>
<th id="2029419615C2" style="width:75px" class="column-headers-background">C</th>
<th id="2029419615C3" style="width:60px" class="column-headers-background">D</th>
<th id="2029419615C4" style="width:60px" class="column-headers-background">E</th>
<th id="2029419615C5" style="width:60px" class="column-headers-background">F</th>
<th id="2029419615C6" style="width:60px" class="column-headers-background">G</th>
<th id="2029419615C7" style="width:185px" class="column-headers-background">H</th>
<th id="2029419615C8" style="width:196px" class="column-headers-background">I</th>
<th id="2029419615C9" style="width:60px" class="column-headers-background">J</th>
<th id="2029419615C10" style="width:74px" class="column-headers-background">K</th>
<th id="2029419615C11" style="width:60px" class="column-headers-background">L</th>
</tr>
</thead>
<tbody>
<tr style='height:19px;'>
<th id="2029419615R0" style="height: 19px;" class="row-headers-background">
<div class="row-header-wrapper" style="line-height: 19px;">1</div>
</th>
<td class="s0">drank</td>
<td class="s0">wdate</td>
<td class="s0">mdate</td>
<td class="s0">ddate</td>
<td class="s0">mspan</td>
<td class="s1">dcol</td>
<td class="s2 softmerge">
<div class="softmerge-inner" style="width: 59px; left: -3px;">patientid</div>
</td>
<td class="s0">patientname</td>
<td class="s1">bedname</td>
<td class="s2 softmerge">
<div class="softmerge-inner" style="width: 59px; left: -3px;">bedstate</div>
</td>
<td class="s0">beddate</td>
<td class="s0">bedid</td>
</tr>
<tr style='height:19px;'>
<th id="2029419615R1" style="height: 19px;" class="row-headers-background">
<div class="row-header-wrapper" style="line-height: 19px;">2</div>
</th>
<td class="s3">0</td>
<td class="s3">Mo</td>
<td class="s3">June</td>
<td class="s3">23</td>
<td class="s3">30</td>
<td class="s3">1</td>
<td class="s3">623681</td>
<td class="s3">Sam Smith</td>
<td class="s4">Bed 1</td>
<td class="s5 softmerge">
<div class="softmerge-inner" style="width: 59px; left: -3px;">Occupied</div>
</td>
<td class="s3">6/23/2016</td>
<td class="s3">84</td>
</tr>
<tr style='height:19px;'>
<th id="2029419615R2" style="height: 19px;" class="row-headers-background">
<div class="row-header-wrapper" style="line-height: 19px;">3</div>
</th>
<td class="s3">0</td>
<td class="s3">Mo</td>
<td class="s3">June</td>
<td class="s3">23</td>
<td class="s3">30</td>
<td class="s3">2</td>
<td class="s3">570853</td>
<td class="s3">Doris Grey</td>
<td class="s3">Bed 2</td>
<td class="s3">Planned</td>
<td class="s3">6/23/2016</td>
<td class="s3">85</td>
</tr>
<tr style='height:19px;'>
<th id="2029419615R3" style="height: 19px;" class="row-headers-background">
<div class="row-header-wrapper" style="line-height: 19px;">4</div>
</th>
<td class="s3">0</td>
<td class="s3">Mo</td>
<td class="s3">June</td>
<td class="s3">23</td>
<td class="s3">30</td>
<td class="s3">0</td>
<td class="s6"></td>
<td class="s3"></td>
<td class="s3">Bed 3</td>
<td class="s3">NULL</td>
<td class="s3">6/23/2016</td>
<td class="s3">86</td>
</tr>
<tr style='height:19px;'>
<th id="2029419615R4" style="height: 19px;" class="row-headers-background">
<div class="row-header-wrapper" style="line-height: 19px;">5</div>
</th>
<td class="s3">1</td>
<td class="s3">Tu</td>
<td class="s3">June</td>
<td class="s3">24</td>
<td class="s3">30</td>
<td class="s3">1</td>
<td class="s3">623681</td>
<td class="s3">Sam Smith</td>
<td class="s4">Bed 1</td>
<td class="s5 softmerge">
<div class="softmerge-inner" style="width: 59px; left: -3px;">Occupied</div>
</td>
<td class="s3">6/24/2016</td>
<td class="s3">84</td>
</tr>
<tr style='height:19px;'>
<th id="2029419615R5" style="height: 19px;" class="row-headers-background">
<div class="row-header-wrapper" style="line-height: 19px;">6</div>
</th>
<td class="s3">1</td>
<td class="s3">Tu</td>
<td class="s3">June</td>
<td class="s3">24</td>
<td class="s3">30</td>
<td class="s3">2</td>
<td class="s3">570853</td>
<td class="s3">Doris Grey</td>
<td class="s4">Bed 2</td>
<td class="s5 softmerge">
<div class="softmerge-inner" style="width: 59px; left: -3px;">Occupied</div>
</td>
<td class="s3">6/24/2016</td>
<td class="s3">85</td>
</tr>
<tr style='height:19px;'>
<th id="2029419615R6" style="height: 19px;" class="row-headers-background">
<div class="row-header-wrapper" style="line-height: 19px;">7</div>
</th>
<td class="s3">1</td>
<td class="s3">Tu</td>
<td class="s3">June</td>
<td class="s3">24</td>
<td class="s3">30</td>
<td class="s3">0</td>
<td class="s6"></td>
<td class="s3"></td>
<td class="s3">Bed 3</td>
<td class="s3">NULL</td>
<td class="s3">6/24/2016</td>
<td class="s3">86</td>
</tr>
<tr style='height:19px;'>
<th id="2029419615R7" style="height: 19px;" class="row-headers-background">
<div class="row-header-wrapper" style="line-height: 19px;">8</div>
</th>
<td class="s3">2</td>
<td class="s3">We</td>
<td class="s3">June</td>
<td class="s3">25</td>
<td class="s3">30</td>
<td class="s3">1</td>
<td class="s3">623681</td>
<td class="s3">Sam Smith</td>
<td class="s4">Bed 1</td>
<td class="s5 softmerge">
<div class="softmerge-inner" style="width: 59px; left: -3px;">Occupied</div>
</td>
<td class="s3">6/25/2016</td>
<td class="s3">84</td>
</tr>
<tr style='height:19px;'>
<th id="2029419615R8" style="height: 19px;" class="row-headers-background">
<div class="row-header-wrapper" style="line-height: 19px;">9</div>
</th>
<td class="s3">2</td>
<td class="s3">We</td>
<td class="s3">June</td>
<td class="s3">25</td>
<td class="s3">30</td>
<td class="s3">2</td>
<td class="s3">570853</td>
<td class="s3">Doris Grey</td>
<td class="s4">Bed 2</td>
<td class="s5 softmerge">
<div class="softmerge-inner" style="width: 59px; left: -3px;">Occupied</div>
</td>
<td class="s3">6/25/2016</td>
<td class="s3">85</td>
</tr>
<tr style='height:19px;'>
<th id="2029419615R9" style="height: 19px;" class="row-headers-background">
<div class="row-header-wrapper" style="line-height: 19px;">10</div>
</th>
<td class="s3">2</td>
<td class="s3">We</td>
<td class="s3">June</td>
<td class="s3">25</td>
<td class="s3">30</td>
<td class="s3">0</td>
<td class="s6"></td>
<td class="s3"></td>
<td class="s3">Bed 3</td>
<td class="s3">NULL</td>
<td class="s3">6/25/2016</td>
<td class="s3">86</td>
</tr>
<tr style='height:19px;'>
<th id="2029419615R10" style="height: 19px;" class="row-headers-background">
<div class="row-header-wrapper" style="line-height: 19px;">11</div>
</th>
<td class="s3">3</td>
<td class="s3">Th</td>
<td class="s3">June</td>
<td class="s3">26</td>
<td class="s3">30</td>
<td class="s3">1</td>
<td class="s3">623681</td>
<td class="s3">Sam Smith</td>
<td class="s4">Bed 1</td>
<td class="s5 softmerge">
<div class="softmerge-inner" style="width: 59px; left: -3px;">Occupied</div>
</td>
<td class="s3">6/26/2016</td>
<td class="s3">84</td>
</tr>
<tr style='height:19px;'>
<th id="2029419615R11" style="height: 19px;" class="row-headers-background">
<div class="row-header-wrapper" style="line-height: 19px;">12</div>
</th>
<td class="s3">3</td>
<td class="s3">Th</td>
<td class="s3">June</td>
<td class="s3">26</td>
<td class="s3">30</td>
<td class="s3">2</td>
<td class="s3">570853</td>
<td class="s3">Doris Grey</td>
<td class="s4">Bed 2</td>
<td class="s5 softmerge">
<div class="softmerge-inner" style="width: 59px; left: -3px;">Occupied</div>
</td>
<td class="s3">6/26/2016</td>
<td class="s3">85</td>
</tr>
<tr style='height:19px;'>
<th id="2029419615R12" style="height: 19px;" class="row-headers-background">
<div class="row-header-wrapper" style="line-height: 19px;">13</div>
</th>
<td class="s3">3</td>
<td class="s3">Th</td>
<td class="s3">June</td>
<td class="s3">26</td>
<td class="s3">30</td>
<td class="s3">0</td>
<td class="s7"></td>
<td class="s3"></td>
<td class="s3">Bed 3</td>
<td class="s3">NULL</td>
<td class="s3">6/26/2016</td>
<td class="s3">86</td>
</tr>
</tbody>
</table>
</div>





The XML is then in the style

QueryResults ReportName="BedStateGraphical" ReportID="160">
<FieldNames>
<Field name="drank"/>
<Field name="wdate"/>
<Field name="mdate"/>
<Field name="ddate"/>
<Field name="mspan"/>
<Field name="dcol"/>
<Field name="PatientID"/>
<Field name="patientname"/>
<Field name="bedname"/>
<Field name="bedstate"/>
<Field name="beddate"/>
<Field name="bedid"/>
</FieldNames>
<Data RecordCount="112">
<DataRow index="1">
<drank>0</drank>
<wdate>Mo</wdate>
<mdate>June</mdate>
<ddate>23</ddate>
<mspan>9</mspan>
<dcol>1</dcol>
<PatientID>623681</PatientID>
<patientname>Sam Smith</patientname>
<bedname>Bed 1</bedname>
<bedstate>Occupied</bedstate>
<beddate>23/07/2016 00:00:00</beddate>
<bedid>84</bedid>
</DataRow>
<DataRow index="2">
<drank>0</drank>
<wdate>Mo</wdate>
<mdate>June</mdate>
<ddate>23</ddate>
<mspan>11</mspan>
<dcol>1</dcol>
<PatientID>570853</PatientID>
<patientname>Doris Grey</patientname>
<bedname>Bed 2</bedname>
<bedstate>Planned</bedstate>
<beddate>23/07/2016 00:00:00</beddate>
<bedid>85</bedid>
</DataRow>


drank was intended as a ranking column to give me each column, mspan as a calculation of a colspan for that patient. I have full control over the query and can redesign this as needed. Essentially for each client I get a start date and an end date, bed id, and a status.

I started with a fairly simple piece of XSLT:

<xsl:key name="drank" match="//DataRow/drank/text()" use="." />
<xsl:template match="QueryResults">
<div style="margin: 2px; ">
<div style="width: 900px; background-color: #29A2CE; color: #FFFFFF; padding: 5px; font-size: large; font-weight: bold">
Carenotes Bed State Report </div>
<table style="width: 900px;padding-left:20px;">
<tr>
<xsl:for-each select="//DataRow/drank/text()[generate-id() = generate-id(key('drank',.)[1])]">
<td>
<xsl:value-of select="//DataRow/ddate" />
</td>
</xsl:for-each>
</tr>
</table>


This gives me the right number of columns, but the ddate selected is always the same day of the week.

XSL isn't my strong point, and I am limited by the application to V1 standards.

Can anyone give me some help / pointers / advice / solution?

UPDATE:

Thanks to Martin, I have now been able to create the framework for the table. The next challenge is to populate the cells with the patient data. Currently I am trying this:

<table style="width: 900px;padding-left:20px;">
<tr>
<td>
</td>
<xsl:for-each select="//DataRow[generate-id() = generate-id(key('mdate',mdate)[1])]">
<td>
<xsl:attribute name="colspan">
<xsl:value-of select="mspan" />
</xsl:attribute>
<xsl:value-of select="mdate" />
</td>
</xsl:for-each>
</tr>
<tr>
<td>
</td>
<xsl:for-each select="//DataRow[generate-id() = generate-id(key('drank',drank)[1])]">
<td>
<xsl:value-of select="ddate" />
</td>
</xsl:for-each>
</tr>
<xsl:for-each select="//DataRow[generate-id() = generate-id(key('bedid',bedid)[1])]">
<tr>
<td>
<xsl:value-of select="bedname" />
</td>
<xsl:for-each select="//DataRow[generate-id() = generate-id(key('drank',drank)[1])]">
<td>
<xsl:value-of select="dcol" />
</td>
</xsl:for-each>
</tr>
</xsl:for-each>
</table>


This is based on 3 keys:

<xsl:key name="drank" match="DataRow" use="drank" />
<xsl:key name="mdate" match="DataRow" use="mdate" />
<xsl:key name="bedid" match="DataRow" use="bedid" />


However the loop that gets the value of dcol is only returning zeros, rather than a mix of 1s and zeros to represent which days are booked. Of course, what I really want to do, is to colspan across the dates of a booking so that I can put in the patient name and other data, as well as hyperlinking to the booking document.

My brain is running out of ideas for this part however!

Answer Source

Use a relative path, so replace <xsl:value-of select="//DataRow/ddate" /> with <xsl:value-of select="../../ddate"/>.

I would probably restructure the key to <xsl:key name="drank" match="DataRow" use="drank" /> and then the grouping to

  <xsl:template match="QueryResults">
    <div style="margin: 2px; ">
      <div style="width: 900px;  background-color: #29A2CE; color: #FFFFFF; padding: 5px; font-size: large; font-weight: bold">
      Carenotes Bed State Report     </div>
      <table style="width: 900px;padding-left:20px;">
        <tr>
          <xsl:for-each select="//DataRow[generate-id() = generate-id(key('drank',drank)[1])]">
            <td>
              <xsl:value-of select="ddate" />
            </td>
          </xsl:for-each>
        </tr>
      </table>