asked
how to read HL7 ADT Message From SQL Server
Dear SQL Reader I have a one Complete HL7 data in single Column in SQL SERVER table, I want to read SQL QUERY from that single to populate all the HL7 fields and sub fields as separate columns , can any one of come across in your previous so plz let me know the dynamic query to get it done. Example: MSH|^~\&|^NISTLabs^L-CL^|^NIST^L-CL|^^|^^|20100407121000||ORU^R01^ORU_R01|20100407121000|P|2.5.1|0001||AL||||MT-ORU-2_R1 PID|1|7777|PT22^^^^AN||Daniel^Scott^^^^^L||19430309|M|||834 Ocean Vista Avenue^Apt. 202^Santa Coloradonica^CA^90401||3105552233 ORC|RE|||ORD003-1^EHR-System OBR|1|1^EHR-System|LAB003^^NIST^L-CL|087-0714^Lipid profile^99Lab^80061^Lipid profile^C4|||20100407151000||||L|||||2^Robinson^William^E^^Dr^MD^^^^^^EI||||RO||20100407121000||CH|F OBX|1|NM|14438-6^Total cholesterol^LN||162|mg/dl|<200||||F|||20100407121000||^DOE^JOHN|||||||Aloha Laboratories^^^^^CLIA^^^^10D987432|575 Luau Street^^Honolulu^Hawaii^96813|^Smith^John^^^Dr.^MD SPM||||||||||||||||||||||||HEM^Hemolyzed| OBX|2|NM|48090-5^HDL cholesterol^LN||43|mg/dl|>=40|H|||F|||20100407121000||^DOE^JOHN|||||||Aloha Laboratories^^^^^CLIA^^^^10D987432|575 Luau Street^^Honolulu^Hawaii^96813|^Smith^John^^^Dr.^MD SPM||||||||||||||||||||||||HEM^Hemolyzed| OBX|3|NM|55440-2^LDL cholesterol^LN||84|mg/dl|<100||||F|||20100407121000||^DOE^JOHN|||||||Aloha Laboratories^^^^^CLIA^^^^10D987432|575 Luau Street^^Honolulu^Hawaii^96813|^Smith^John^^^Dr.^MD SPM||||||||||||||||||||||||HEM^Hemolyzed| OBX|4|NM|14445-1^Triglycerides^LN||127|mg/dl|<150||||F|||20100407121000||^DOE^JOHN|||||||Aloha Laboratories^^^^^CLIA^^^^10D987432|575 Luau Street^^Honolulu^Hawaii^96813|^Smith^John^^^Dr.^MD SPM||||||||||||||||||||||||HEM^Hemolyzed| assume that the above file inserted in Single column in sql table with Log id as 1, i wan to populate it as single row and populate all the information in separate columns, I am looking for T SQL Statement , No XML , NO Biztalk please, Only direct T SQL QUERY
This looks like a basic string-parsing requirement. I know you said you want a T-SQL solution, but that's not really the ideal tool for that type of work. If you're just trying to avoid an extra cost for another tool, perhaps you could try SSIS because it comes with SQL Server. I have had to do many tasks like this, and I've had great success using a C# script component in a data flow. I split the string and then set the column values in an output row. Response to comment:
I don't think you'll have to worry about SSIS metadata changes any more than you would have to consider column definition changes in your destination table. Here's how I've done this: 1. Configure input text file with 1 wide column. 2. Create a data flow with the text file as the source. 3. Add a transformation script component. 4. Split the text using the character separator (appears to be a pipe in your case). 5. Add a new output row. 6. Set output column values from elements of the array or list or whatever you split into. You would only need to change the data flow if the metadata of the input file or output table change. For the file, you're treating it as 1 string column, so you would only need to change that if you chose a length that was too small. For the destination, you need to know the column data types and sizes in advance for either T-SQL or SSIS.
I don't think you'll have to worry about SSIS metadata changes any more than you would have to consider column definition changes in your destination table. Here's how I've done this: 1. Configure input text file with 1 wide column. 2. Create a data flow with the text file as the source. 3. Add a transformation script component. 4. Split the text using the character separator (appears to be a pipe in your case). 5. Add a new output row. 6. Set output column values from elements of the array or list or whatever you split into. You would only need to change the data flow if the metadata of the input file or output table change. For the file, you're treating it as 1 string column, so you would only need to change that if you chose a length that was too small. For the destination, you need to know the column data types and sizes in advance for either T-SQL or SSIS.
Hello Tom Thank you so much for reply, If we go with SSIS then whenever the sample HL7 files changes i mean each time HL7 file message will differ , in that case the Meta data of the ssis package source/destination will change and mapping needs to done manually instead if i have a dynamic T SQL query which can populate as per the HL7 ADT message that will be great for me, sample files
http://emrpms.blogspot.com/2012/08/sample-hl7-files.html?showComment=1446747467128#c5471589729823902788 Thanks in Advance I look forwared to reading from you. RAj
Any actual T-SQL solution for this?
@Romans109: I'm not going to give you the full text for an answer, mainly because you haven't asked a separate question; however, the steps are: (1) Create a temporary table to hold an import of the raw data from the file IF EXISTS (SELECT * FROM sys.tables WHERE name = 'TextImport') BEGIN DROP TABLE TextImport; END; CREATE TABLE TextImport (TextLine nvarchar(max)); (2) Bring your data from the source file into the TextImport table: BULK INSERT TextImport FROM '< >' WITH (FIELDTERMINATOR='|', ROWTERMINATOR='\n', TABLOCK); (3) When you've satisfied yourself that the data gets through this stage OK, then you can use something like @Jeff Moden's "[DelimitedSplit8K][1]" function to shred the data: SELECT MAX(CASE WHEN x.ItemNumber = 1 THEN x.Item ELSE NULL END) AS Field1, MAX(CASE WHEN x.ItemNumber = 2 THEN x.Item ELSE NULL END) AS Field2... ... FROM ( SELECT TextLine, ItemNumber, Item FROM TextImport CROSS APPLY DelimitedSplit8K(TextLine, '|') ) x GROUP BY x.TextLine You might need to filter the output above in order to remove blank lines or header lines; you'll also want to insert the data into some other table for processing into your live data. I leave that to your imagination. [1]:
http://www.sqlservercentral.com/articles/Tally+Table/72993/
Thinking about it, I probably could omit the FIELDTERMINATOR from the BULK INSERT... but I haven't got time to play with this now.
19 People are following this question.
