Help needed with SQL stored procedure and XML

poops66 · 18-02-2012 9:18am #1

Hi,
I have a test to complete by sunday afternoon. Part of which is to create a stored procedure in MS SQL that would populate tables from a given XML file which is given to stored procedure as a 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

Thanks

biko · 18-02-2012 9:22am

What's the difference between the ones you can do and the actual one that doesn't work?

Did you google how to write stored procs? http://www.4guysfromrolla.com/webtech/111499-1.shtml

Colonel Panic · 18-02-2012 9:26am

You should post the code, files, errors and what you've tried if you want anyone here to be able to help!

poops66 · 18-02-2012 9:42am

biko wrote: »

What's the difference between the ones you can do and the actual one that doesn't work?

Did you google how to write stored procs? http://www.4guysfromrolla.com/webtech/111499-1.shtml

Thank you for your reply. The difference is that all simple once are just set of records for one table. The one I have to do is kinda multi table

<MsgId>8CC44476-58AC-41A1-BC13-E8833F94E6C0</MsgId> <MsgIssueDate>20080125125700</MsgIssueDate> <MsgSender>0080170006073333333</MsgSender> <MsgRecipient>0080000000257099999</MsgRecipient>
<Order>
<GUID>.Order1 guid.</GUID>
<OrderDate>20120102</OrderDate>
<fromSupplier>
<SuppCode>SUPP1</Suppcode>
<SuppName>SUPP Name</SuppName>
<SuppPhone>01 23537</SuppPhone>
<fromSupplier>
<ToCust>
<CustCode>Cust1</Custcode>
<CustName>Cust Name</ <Custname>
<CustPhone>098 762</CustPhone>
<ToCustr>
<OrderItem>
<GUID>.guid for item1.</GUID>
<ItemCode>item1</Itemcode>
<ItemName>Item1 name</ItemName>
<ItemQty>20</ItemQty>
</OrderItem>
<OrderItem>
<GUID>.guid for item2.</GUID>
<ItemCode>item2</Itemcode>
<ItemName>Item2 name</ItemName>
<ItemQty>43</ItemQty>
</OrderItem>
</Order>

<Order>
<GUID>.Order2 guid.</GUID>
<OrderDate>2011102</OrderDate>
<fromSupplier>
<SuppCode>SUPP3</Suppcode>
<SuppName>SUPP3 Name</SuppName>
<SuppPhone>343537</SuppPhone>
<fromSupplier>
<ToCust>
<CustCode>Cust2</Custcode>
<CustName>Cust2 Name</ <Custname>
<CustPhone>098 734362</CustPhone>
<ToCustr>
<OrderItem>
<GUID>.guid for item1.</GUID>
<ItemCode>item1</Itemcode>
<ItemName>Item1 name</ItemName>
<ItemQty>20</ItemQty>
</OrderItem>
<OrderItem>
<GUID>.guid for item2.</GUID>
<ItemCode>item2</Itemcode>
<ItemName>Item2 name</ItemName>
<ItemQty>43</ItemQty>
</OrderItem>
</Order>

I need to check that same information is not imported twice. If items don't exist create them in items table, if customer doesn't exist create them, if supplier doesn't exist create them and insert order info into order table.
is this difficult to do?
Thanks

Giblet · 18-02-2012 10:48am

What data type are you storing the XML as? You should be able to shred the incoming XML using the XML datatype.

SET @data = 
N'<MsgId>8CC44476-58AC-41A1-BC13-E8833F94E6C0</MsgId> <MsgIssueDate>20080125125700</MsgIssueDate> <MsgSender>0080170006073333333</MsgSender> <MsgRecipient>0080000000257099999</MsgRecipient>
<Order>
<GUID>.Order1 guid.</GUID>
<OrderDate>20120102</OrderDate>
<fromSupplier>
<SuppCode>SUPP1</Suppcode>
<SuppName>SUPP Name</SuppName>
<SuppPhone>01 23537</SuppPhone>
<fromSupplier>
<ToCust>
<CustCode>Cust1</Custcode>
<CustName>Cust Name</ <Custname>
<CustPhone>098 762</CustPhone>
<ToCustr>
<OrderItem>
<GUID>.guid for item1.</GUID>
<ItemCode>item1</Itemcode>
<ItemName>Item1 name</ItemName>
<ItemQty>20</ItemQty>
</OrderItem>
<OrderItem>
<GUID>.guid for item2.</GUID>
<ItemCode>item2</Itemcode>
<ItemName>Item2 name</ItemName>
<ItemQty>43</ItemQty>
</OrderItem>
</Order>

<Order>
<GUID>.Order2 guid.</GUID>
<OrderDate>2011102</OrderDate>
<fromSupplier>
<SuppCode>SUPP3</Suppcode>
<SuppName>SUPP3 Name</SuppName>
<SuppPhone>343537</SuppPhone>
<fromSupplier>
<ToCust>
<CustCode>Cust2</Custcode>
<CustName>Cust2 Name</ <Custname>
<CustPhone>098 734362</CustPhone>
<ToCustr>
<OrderItem>
<GUID>.guid for item1.</GUID>
<ItemCode>item1</Itemcode>
<ItemName>Item1 name</ItemName>
<ItemQty>20</ItemQty>
</OrderItem>
<OrderItem>
<GUID>.guid for item2.</GUID>
<ItemCode>item2</Itemcode>
<ItemName>Item2 name</ItemName>
<ItemQty>43</ItemQty>
</OrderItem>
</Order>';
 

SELECT T.order.query('ItemCode').value('.', 'VARCHAR(20)') AS item_id,
       T.order.query('ItemName').value('.', 'VARCHAR(20)') AS item_name
FROM @data.nodes('Order/OrderItem') AS T(order);

So you could use a where item_id not in (select item_id from itemstable) as a clause?
Your xml above seems invalid though.

poops66 · 18-02-2012 10:53am

Giblet wrote: »

What data type are you storing the XML as?

Hi, I would probably look stupid but what do you mean?

Giblet · 18-02-2012 11:13am

The parameter would be something like
@XML XML, or is it @XML varchar(8000) ?

poops66 · 18-02-2012 11:15am

Giblet wrote: »

The parameter would be something like
@XML XML, or is it @XML varchar(8000) ?

@XML XML

Giblet · 18-02-2012 11:39am

So how are you processing the XML in the stored procedure?

poops66 · 18-02-2012 11:56am

Giblet wrote: »

So how are you processing the XML in the stored procedure?

So far I am just trying to understand how to parse it...

@prescriptiondata XML
select @prescriptiondata.query('/Prescription/PxItem')
This gives me XML output...Don't know what I can do with it

select
Col.value('@PrescriptionDate','nvarchar(10)') as PrescriptionDate1
from
@prescriptiondata.nodes('/Prescription') T(Col)
This gives me correct number of records but NULL values...

Could you show me the way

Giblet · 18-02-2012 2:39pm

declare @xml XML

set @xml = 'SOME XML'

SELECT T.orders.query('Guid').value('.', 'VARCHAR(255)') AS item_id
FROM @xml.nodes('EtpPrescribe/Prescription/PxItem') AS T(orders);

poops66 · 18-02-2012 5:54pm

Thank you so much for that. One last question. Could you please give me an example on how to use result of T.orders.query to insert into another table. I would so appreciate it.
Thanks a lot

Giblet · 18-02-2012 7:14pm

It would just be the same as inserting from any select.

insert into <tablename>
SELECT T.orders.query('Guid').value('.', 'VARCHAR(255)'), ....etc


...where T.orders.query('Guid').value('.', 'VARCHAR(255)') not in (select guid from <tablename>)

to prevent dupes.

You could also use a staging table to hold ALL of the data, which is cleared down all the time, then use that table for operations with the other table. Will most likely be faster rather that shredding the XML on joins and comparisons.

Giblet · 18-02-2012 7:22pm

For demo purposes and small projects, this is all fine. But on a large scale you will want to look into SSIS and creating ETL packages. I hate SSIS with a passion, it's the worst part of my job.

poops66 · 18-02-2012 7:37pm

Dear Giblet, Thank you so much. Can I ask again?
so I need to insert prescription record

ALTER procedure [dbo].[sp_mytest66]
@XML XML
as
insert into dbo.prescriptions
SELECT
T1.Prescription.query('Guid').value('.', 'uniqueidentifier'),
T1.Prescription.query('PrescriptionDate').value('.', 'smalldatetime'),
'111',
'222'
FROM @xml.nodes('EtpPrescribe/Prescription') AS T1(Prescription)

This inserts 2 records. But instead of '111' and '222' I need to get IdValue from 'EtpPrescribe/Prescription/Prescriber' and NHS from 'EtpPrescribe/Prescription/Patient' nodes that are linked to that prescription...
How to link them?
Thanks a lot.

Giblet · 18-02-2012 7:46pm

SELECT T.orders.query('PxItem/Guid').value('.', 'uniqueidentifier') AS id,
T.orders.query('Prescriber/IdValue').value('.', 'varchar(255)') AS idvalue,
T.orders.query('Patient/NHS').value('.', 'varchar(255)') AS nhs
FROM @xml.nodes('EtpPrescribe/Prescription') AS T(orders);

poops66 · 18-02-2012 8:36pm

Thank you very much.
one more thing to clarify...

declare @XML XML
set @XML='xml goes here'
SELECT T1.Items.query('Guid').value('.', 'uniqueidentifier'), 
T1.Items.query('Drug_Code').value('.', 'varchar(10)'),
'How do I get Prescription Guid here?'
FROM @xml.nodes('EtpPrescribe/Prescription/PxItem') AS T1(Items)

there are 2 prescriptions, one has 2 items and another one 3. So how in select above which is returning 5 records get 'parent' GUID?
Thanks. I hope this will be the last question.

Giblet · 18-02-2012 8:41pm

Well you set your context here
FROM @xml.nodes('EtpPrescribe/Prescription/PxItem') AS T1(Items)

So you need to set it up one level to

FROM @xml.nodes('EtpPrescribe/Prescription') AS T1(prescription)

And then select

T1.prescription.query('Guid')
T1.prescription.query('PxItem/Guid')
T1.prescription.query('PxItem/Drug_Code')

EDIT:
Oh I get you now. Give me a few mins

Giblet · 18-02-2012 9:14pm

SELECT Prescription.value('.','varchar(100)') as PID, 
Items.value('Guid[1]','varchar(100)'),
Items.value('Drug_Code[1]','varchar(100)')
from @xml.nodes('EtpPrescribe/Prescription') AS T(Prescription)
cross apply T.Prescription.nodes('PxItem') AS T2(Items)

poops66 · 18-02-2012 9:27pm

I am really sorry, but this is not clear to me. How in one select statement display 5 PxItems (as per XML file) with details and attach paren GUID of Prescription?

Giblet · 18-02-2012 9:34pm

This code does that, it returns 5 rows, each with the Prescription Guid
You need to select the root node, then cross apply with subnodes and only use values as query() selects the first child.
Run the following.

SELECT 
Items.value('Guid[1]','uniqueidentifier') AS Item_GUID,
Items.value('Drug_Code[1]','varchar(100)') as Drug_Code,
Prescription.value('Guid[1]','uniqueidentifier') as Prescription_GUID,
Prescription.value('PrescriptionDate[1]','smalldatetime') as Prescription_Date
from @xml.nodes('EtpPrescribe/Prescription') AS T(Prescription)
cross apply T.Prescription.nodes('PxItem') AS T2(Items)

Giblet · 18-02-2012 10:21pm

OpenXML is going to be a little harder. What is the issue with what I posted?

Don't use ../ use the path

PrescriptionGUID uniqueidentifier '/EtpPrescribe/Prescription/PxItem/Guid',

poops66 · 18-02-2012 10:23pm

After changing to

SELECT *
FROM   OPENXML (@idoc, '/EtpPrescribe/Prescription/PxItem')
         WITH (--OrderID       int         '../@OrderID',
               PrescriptionGUID  uniqueidentifier '../GUID',
               PrescriptionDate   smalldatetime    '../PrescriptionDate',
			   Drug_GUID   uniqueidentifier         'GUID',
               Drug_Desc   varchar(100)         'Description',
               ProductQuantity         int         'ProductQuantity')

result is

(5 row(s) affected)

[/code]

but still no GUID values.

Giblet · 18-02-2012 10:31pm

You still need to cross apply, this is a SQLServer 2000 method, any reason you can't use my version?

poops66 · 18-02-2012 10:36pm

The only reason is that I did not completely uderstand yours...
So correct me if I am wrong:
To get that XML file into Database (Tables Prescriptions and PrescriptionItems)
1)

@XML XML
as

insert into dbo.prescriptions

SELECT T1.Prescription.query('Guid').value('.', 'uniqueidentifier'), --AS Prescription_guid,
T1.Prescription.query('PrescriptionDate').value('.', 'smalldatetime'),-- AS PrescriptionDate
T1.Prescription.query('Prescriber/IdValue').value('.', 'varchar(6)'),
T1.Prescription.query('Patient/NHS').value('.', 'varchar(10)')
FROM @xml.nodes('EtpPrescribe/Prescription') AS T1(Prescription)

where T1.Prescription.query('Guid').value('.', 'uniqueidentifier') 
not in (select P_guid from Prescriptions)

which will insert Prescriptions records

2) using ur method insert Prescriptions items

@XML XML
as
SELECT 
Prescription.value('Guid[1]','uniqueidentifier') as Prescription_GUID,
Items.value('Guid[1]','uniqueidentifier') AS Item_GUID,
Items.value('Drug_Code[1]','varchar(100)') as Drug_Code,

Prescription.value('PrescriptionDate[1]','smalldatetime') as Prescription_Date
from @xml.nodes('EtpPrescribe/Prescription') AS T(Prescription)
cross apply T.Prescription.nodes('PxItem') AS T2(Items)

Once again, thank you for all your help

Giblet · 18-02-2012 10:49pm

No, just this

insert into dbo.prescriptions
SELECT 
	Prescription.value('Guid[1]','uniqueidentifier') AS Prescription_GUID,
	Prescription.value('PrescriptionDate[1]','smalldatetime') AS Prescription_Date,
	Prescriber.value('IdValue[1]','varchar(6)') as Prescriber_Id,
	Patient.value('NHS[1]','varchar(10)') AS NHS
FROM @xml.nodes('EtpPrescribe/Prescription') AS T(Prescription)
  cross apply T.Prescription.nodes('PxItem') AS T2(Items)
  cross apply T.Prescription.nodes('Prescriber') AS T3(Prescriber)
  cross apply T.Prescription.nodes('Patient') AS T4(Patient)

WHERE Prescription.value('Guid[1]','uniqueidentifier') not in
  (SELECT P_guid FROM Prescriptions)

poops66 · 18-02-2012 11:06pm

But this will only insert into one table. I need prescription items to go into one table and prescription details into another. And there will be foreing key on Prescription_guid. So I am right in saying that I need to do it in 2 setps?
Thanks

Giblet · 18-02-2012 11:13pm

Just redo what I've done in the last one to make it work for the data you need to populate the items first. You will need two separate insert statements.

poops66 · 18-02-2012 11:14pm

That is it so. Thanks a million for your help.

Help needed with SQL stored procedure and XML

Comments