Asked  7 Months ago    Answers:  5   Viewed   46 times

I'm creating a HL7 Continuity of Care Document (CCD) using FOR XML statements in SQL Server 2008 R2.

I've done A LOT with this method, but this is the first time I have to represent part of the data in a HTML table, which is giving me trouble.

So, I have the following information in a table:

  Problem  |   Onset    | Status
---------------------------------
  Ulcer    | 01/01/2008 | Active
  Edema    | 02/02/2005 | Active

and I'm trying to render the following

<tr>
    <th>Problem</th>
    <th>Onset</th>
    <th>Status</th>
</tr>
<tr>
    <td>Ulcer</td>
    <td>01/01/2008</td>
    <td>Active</td>
</tr>
<tr>
    <td>Edema</td>
    <td>02/02/2005</td>
    <td>Active</td>
</tr>

I'm using this query:

SELECT    p.ProblemType AS "td"
    , p.Onset AS "td"
    , p.DiagnosisStatus AS "td"
FROM tblProblemList p
WHERE p.PatientUnitNumber = @PatientUnitNumber
FOR XML PATH('tr')

And I keep getting the following:

<tr>
  <td>Ulcer2008-01-01Active</td>
</tr>
<tr>
  <td>Edema2005-02-02Active</td>
</tr>

Anyone got any advice?

 Answers

69
select 
  (select p.ProblemType     as 'td' for xml path(''), type),
  (select p.Onset           as 'td' for xml path(''), type),
  (select p.DiagnosisStatus as 'td' for xml path(''), type)
from tblProblemList p
where p.PatientUnitNumber = @PatientUnitNumber
for xml path('tr')

To add the header as well you can use union all.

select 
  (select 'Problem' as th for xml path(''), type),
  (select 'Onset'   as th for xml path(''), type),
  (select 'Status'  as th for xml path(''), type)
union all         
select 
  (select p.ProblemType     as 'td' for xml path(''), type),
  (select p.Onset           as 'td' for xml path(''), type),
  (select p.DiagnosisStatus as 'td' for xml path(''), type)
from tblProblemList p
where p.PatientUnitNumber = @PatientUnitNumber
for xml path('tr')
Tuesday, June 1, 2021
 
jwegner
answered 7 Months ago
22

Here is something that should help you to create the table and get more knowledge of php and mysql.

Also you should move your connection logic and query to the beginning of your process, thus avoiding errors while loading the page and showing a more accurate error than just the mysql_error.

Edit: If your ids are incrementing, then you could add the ORDER BY clause,
change: SELECT * FROM demo LIMIT 10 to: SELECT * FROM demo LIMIT 10 ORDER BY id

<html>
    <head>
        <title>Last 10 Results</title>
    </head>
    <body>
        <table>
        <thead>
            <tr>
                <td>Id</td>
                <td>Name</td>
            </tr>
        </thead>
        <tbody>
        <?php
            $connect = mysql_connect("localhost","root", "root");
            if (!$connect) {
                die(mysql_error());
            }
            mysql_select_db("apploymentdevs");
            $results = mysql_query("SELECT * FROM demo LIMIT 10");
            while($row = mysql_fetch_array($results)) {
            ?>
                <tr>
                    <td><?php echo $row['Id']?></td>
                    <td><?php echo $row['Name']?></td>
                </tr>

            <?php
            }
            ?>
            </tbody>
            </table>
    </body>
</html>
Saturday, May 29, 2021
 
antoniputra
answered 7 Months ago
27

Turns out I didn't want the CTE at all, just a UDF that I call recursively

CREATE FUNCTION PageHierarchyNode(@PageId int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT 
BEGIN RETURN 
  (SELECT ModuleId AS "@ModuleId", PageId AS "@PageId",
    ParentPageId AS "@ParentPageId", PageUrl AS "@PageUrl",
    PageTitle AS "@PageTitle", PageOrder AS "@PageOrder", 
      CASE WHEN ParentPageId=@PageId
      THEN dbo.PageHierarchyNode(PageId)
      END
   FROM dbo.PageHierarchy WHERE ParentPageId=@PageId
   FOR XML PATH('Page'), TYPE)
END

with the SQL that calls the UDF as

SELECT ModuleId AS "@ModuleId", PageId AS "@PageId",
    ParentPageId AS "@ParentPageId", PageUrl AS "@PageUrl",
    PageTitle AS "@PageTitle", PageOrder AS "@PageOrder", 
    dbo.PageHierarchyNode(PageId)
FROM PageHierarchy
WHERE ParentPageId IS NULL
FOR XML PATH('Page'), ROOT('SiteMap'), TYPE
Tuesday, August 3, 2021
 
Saurabh
answered 4 Months ago
13

SQLAlchemy is confused about how to join Manager to Employee because you have multiple foreign keys between the two tables, employee_id and owner_id. In this case, you need to specify the inherit_condition to the mapper explicitly:

class Manager(Employee):
    __tablename__ = 'managers'

    employee_id = Column(Integer, ForeignKey('employees.employee_id'),
                         primary_key=True)
    manager_data = Column(String(50))

    owner_id = Column(Integer, ForeignKey('employees.employee_id'))

    __mapper_args__ = {'polymorphic_identity': 'manager', 'inherit_condition': employee_id == Employee.employee_id}
Wednesday, August 25, 2021
 
Bitwise
answered 3 Months ago
92

I found a solution to add all the namespaces here:

https://stackoverflow.com/a/536781/1306012

Obvious it's not very "nice" style but in my case it works and I haven't found another working solution yet.

SOLUTION

DECLARE @period XML = (
SELECT
'2012' 'period'
FOR XML PATH(''), ROOT ('survey'))

DECLARE @persons XML = (
SELECT
Person.Name 'users/person'
FROM Person
FOR XML PATH(''), ROOT ('company'))

SET @persons.modify('insert sql:variable("@period") as first into (/company)[1]')

-- SOLUTION
SET @persons = replace(cast(@persons as varchar(max)), '<company>', '<company xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.mydomain.com/xmlns/bla/blabla/myschema.xsd" xmlns="">')


SELECT @persons
Thursday, November 18, 2021
 
Chris McCall
answered 2 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share