Blog archive

Nemsis XML Output With Microsoft SQL Server

My last article used C Sharp programming to manipulate patient data.  I received a great comment on Reddit that some agencies use SQL Server to output Nemsis XML.

My previous experience with SQL Server gave me the impression that the data had to be attribute based.  I didn't think node output was supported.  I was wrong.

I have learned how to specify certain elements to be elements and some attributes, which we will need to create Nemsis compliant XML.

This article is using the Nemsis data dictionary defined here: http://nemsis.org/media/nemsis_v3/release-3.4.0/DataDictionary/PDFHTML/DEMEMS/index.html

 

 

FOR XML

To start, we add the "FOR XML PATH('')" to the end of a SQL Query.

 

This outputs the results to XML and it aliases the column to the Nemsis identifier.

Nice, now we need to nest this result into an ePatient node.  We can do this by specifying the Path name.

 

Now the XML is nested under the ePatient node.

 

Patient Name Group

The next node is nested under a node named PatientNameGroup.  To allow this I add a sub query with its own FOR XML PATH.  I specify the name as ePatient.PatientNameGroup.  I also include the TYPE keyword.  This allows the less then and greater then characters to show.

 

The XML output now has the nested names in a group.

 

Mixing Elements and Attributes

The next challenge will be to write attributes.  Nemsis uses attributes on nodes to specify Not Values and Pertinent Negatives.

The @ at symbol is a special character which specifies that a value should be an attribute.  We add the code 7701001 to indicate that the county is not applicable.

 

The XML result is:

 

SQL Server Table

The last step we will cover is adding our SQL Server table and column names to create real world data.  Use "FROM Patient" to specify the patient table name.  Replace the text values with the column names from your patient table.  Be sure to limit your result count if running this against your production database.

 

Support

Has this post been useful to you?

Support me via a Paypal donation http://epcr.allcode.net/donation

Share this article to social media:

Leave your comment