February 18, 2012 at 4:41 am
Hi,
I have a test to complete by sunday afternoon. Part of which is to create a database structure according XML file which is not a problem and create stored procedure in MS SQL that would populate tables from a given XML file which is given to stored procedure as a single parameter. I am new to MS SQL and thought that I should be able for it but .... I tried a lot of simple XML test files and it all worked ok but the one I am given I am not sure if I am approaching it right...
I wonder if somebody could help me out.
I Have XML file attached
Thanks
February 19, 2012 at 5:33 am
alexei.sviridov (2/18/2012)
Hi,I have a test to complete by sunday afternoon........but the one I am given I am not sure if I am approaching it right...
I wonder if somebody could help me out.
Thanks
I am sure that someone could help you out...but you haven't given us any details of what you currently have tried and where you are having problems....??
care to post some more details of what your current "approach" to this problem?
kind regards
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 19, 2012 at 5:41 am
never mind.....hope you pass your test! 😉
http://www.boards.ie/vbulletin/showthread.php?p=77157817
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 19, 2012 at 5:41 am
Hi, thanks for your reply. I think I have it done.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_ImpPrescriptions]
@XML xml
as
insert into ImpHist(MsgID,MsgIssueDate)
SELECT
T.Msg.query('MsgId').value('.', 'uniqueidentifier'),
T.Msg.query('MsgIssueDate').value('.', 'varchar(20)')
FROM @xml.nodes('EtpPrescribe') AS T(Msg)
insert into prescriptions
SELECT
T1.Prescription.query('Guid').value('.', 'uniqueidentifier'),
T1.Prescription.query('PrescriptionDate').value('.', 'smalldatetime'),
T1.Prescription.query('Prescriber/IdValue').value('.', 'varchar(10)'),
T1.Prescription.query('Prescriber/UnstructPersonName').value('.', 'varchar(50)'),
T1.Prescription.query('Prescriber/UnstructTelecomNum').value('.', 'varchar(20)'),
T1.Prescription.query('Patient/NHS').value('.', 'varchar(10)'),
T1.Prescription.query('Patient/StructPersonName/FamilyName').value('.', 'varchar(50)'),
T1.Prescription.query('Patient/StructPersonName/GivenName').value('.', 'varchar(20)'),
T1.Prescription.query('Patient/StructPersonName/FamilyName').value('.', 'varchar(4)'),
T1.Prescription.query('Patient/BirthDate').value('.', 'smalldatetime')
FROM @xml.nodes('EtpPrescribe/Prescription') AS T1(Prescription)
where T1.Prescription.query('Guid').value('.', 'uniqueidentifier')
not in (select P_guid from Prescriptions)
insert into pi_items
SELECT
Items.value('Guid[1]','uniqueidentifier') AS Item_GUID,
Prescription.value('Guid[1]','uniqueidentifier') AS Prescription_GUID,
Items.value('Drug_Code[1]','varchar(10)') AS Drug_code,
Items.value('Description[1]','varchar(100)') AS Description,
Items.value('ProductQuantity[1]','int') AS Qty,
Items.value('Instructions[1]','varchar(100)') AS Instr
FROM @xml.nodes('EtpPrescribe/Prescription') AS T(Prescription)
cross apply T.Prescription.nodes('PxItem') AS T2(Items)
WHERE Items.value('Guid[1]','uniqueidentifier') not in
(SELECT PI_GUID FROM PI_Items)
February 19, 2012 at 6:30 am
alexei
One question : did you properly obfuscate the data in your attachment xml?
if not then please ask the site to remove it immediately.
If you are seeking help with something from your work, you must use made up data, not any real names, ID numbers, or other potentially identifying information.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply