Help needed with SQL stored procedure and XML

poops66

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

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

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

poops66

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

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

Giblet wrote: »

What data type are you storing the XML as?

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

Giblet

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

poops66

Giblet wrote: »

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

@XML XML

Giblet

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

poops66

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

poops66

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

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

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

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

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