September 30, 2011 at 10:46 am
I am writing a process to send XML files to a external party on a daily basis that contain all the orders that were made in that day.
I am using FOR XML auto to generate the XML and SSIS to push the file to target folder.
My question here is before i send the file, i have to load whatever is there in the XML to a table.
This is more of an audit mechanism and if the vendor comes back one day complaining that
an order X was never sent to them; we should be able to easily find if order X was ever generated in the XMLs we had sent them.
Curretnly this is what i am doing.
1) In the first block of SP, Select from orders table as result set and load to Audit table
2) In the next block of SP, Select from orders table as XML and send to vendor
This surely doesnt look like the best way to handle the requirement.
Please suggest any better way to do this...
Thanks,
September 30, 2011 at 11:40 am
If I were doing this, I would probably just store the XML document in your audit table. That way you have a record of EXACTLY what was sent to the third party.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 30, 2011 at 11:41 am
Can't you generate the xml, then read it to find the orders in there for the audit? If it's an audit you want to be sure the xml generation didn't filter out any rows from those you selected for the export, don't you?
September 30, 2011 at 11:58 am
R.P.Rozema (9/30/2011)
Can't you generate the xml, then read it to find the orders in there for the audit? If it's an audit you want to be sure the xml generation didn't filter out any rows from those you selected for the export, don't you?
Shredding the XML document can also inadvertently filter out rows. That's why I recommended storing the XML document itself.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 30, 2011 at 12:40 pm
Store the document you send. That's more verifiable than anything else when it comes to audit trails.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 1, 2011 at 2:11 am
I agree with that: storing the document itself is the best audit you can have. You may however still, next to storing the documents, read the orders from them to keep as an index of which file contains which order. Once you've got hundreds of documents in your audit it can become hard to find the one containing the order that your supplier/customer is complaining about, keeping track of the orders in each file will help in these cases.
October 1, 2011 at 9:11 pm
Thank you!
Convinced to store XML directly in a table.
A follow up question, let's say for example the XML file that's sent has 3 tags as below.
Always a blank field will be sent in the status tag.
<OrderID>1000</OrderID>
<ZipCode>07666</ZipCode>
<Status></Status>
Vendor will send back a file in response which looks like below:
<OrderID>1000</OrderID>
<ZipCode>07666</ZipCode>
<Status>Accepted</Status>
The response will contain a status of the order.
How can i use the response XML to update "Status".
Thanks.
October 2, 2011 at 3:13 am
Better not make the column's type [xml]. If you do make it an [xml] column, you will not be able to store a file that happens to be malformed; i.e. you'll loose the information that was malformed and you will not be able analyse the problem.
October 2, 2011 at 4:41 am
UnionAll (10/1/2011)
Thank you!Convinced to store XML directly in a table.
A follow up question, let's say for example the XML file that's sent has 3 tags as below.
Always a blank field will be sent in the status tag.
<OrderID>1000</OrderID><ZipCode>07666</ZipCode>
<Status></Status>
Vendor will send back a file in response which looks like below:
<OrderID>1000</OrderID><ZipCode>07666</ZipCode>
<Status>Accepted</Status>
The response will contain a status of the order.
How can i use the response XML to update "Status".
Thanks.
The message from your vendor will probably be valid xml, so it will be a document with a single root node, unlike your example. I chose to make Order the root node, possibly your vendor will send multiple orders in a document, so another node may be below that even. You will have to adjust the query accordingly. I've put some comments I thought could be helpfull in the example below. You will have to put in proper error handling and such before production.
-- Example for the audit table, holding all received files.
create table dbo.ReceivedFiles (
ReceivedFilesID int identity(-2147483648 , 1), -- Why waste half of the available range?
ReceivedAt DateTime2 not null default getutcdate(),
ReceivedFile varbinary(max) not null,
constraint PK_ReceivedFiles primary key (ReceivedFilesID)
);
go
-- Now what do you need to do to receive a file and update your
-- orders' statusses from it.
-- Declare storage for the file itself.
declare @ReceivedFile varbinary(max);
-- Declare storage for the order-statuses read from the file.
declare @orders table (
OrderID int not null,
[Status] varchar(10) null
);
-- Read the received file into a local variable. This is one method, many
-- more exist. You'll have to find the one that matches your needs /
-- security model.
select @ReceivedFile = f.BulkColumn
from openrowset( BULK 'd:\test.xml', SINGLE_BLOB) f;
-- First do the auditing: store the received file, assigning it an ID, and
-- marking the receive date time.
insert dbo.ReceivedFiles( ReceivedAt, ReceivedFile)
select getutcdate(), @ReceivedFile;
-- Get the ID of the row we just created.
select @RFID = @@identity;
-- Now collect all the orders plus their reported status from this file.
insert @orders (OrderID, [Status], ReceivedAt)
select x.OrderID, x.[Status], rf.ReceivedAt
from dbo.ReceivedFiles rf
cross apply (
-- Because I chose to store also invalid xml files, I need to
-- do a conversion into xml before I can process the contents.
select convert(xml, rf.ReceivedFile) as [xml]
) converttoxml
cross apply (
-- Read from each 'Order' node both the OrderID and the Status elements.
select o.o.value('OrderID[1]', 'int') as OrderID,
o.o.value('Status[1]', 'varchar(10)') as Status
from converttoxml.xml.nodes('Order') o(o)
) x
where rf.ReceivedFileID = @RFID;
-- Update any unaccepted orders for which we just received an 'Accepted' status.
-- Remark 1: Do not (re-)use the 'Accepted' literal from the message in your Orders table: you may f.e. get
-- at some time more / other partners to communicate with, which may use other values.
-- Remark 2: Do not update your order's status blindly to the message's contents
-- as you may receive at some point 'old messages' from your partner. You
-- don't want to mess up your statusses because of that.
update ord
set Accepted = 1,
AcceptedAt = o.ReceivedAt
from dbo.Orders ord
inner join @Orders o on (o.OrderID = ord.OrderID and o.[Status] = 'Accepted')
where not ord.Accepted = 1;
edit: example of the message format I assumed:
<Order>
<OrderID>1000</OrderID>
<ZipCode>07666</ZipCode>
<Status></Status>
</Order>
October 4, 2011 at 2:02 am
UnionAll (10/1/2011)
Thank you!Convinced to store XML directly in a table.
A follow up question, let's say for example the XML file that's sent has 3 tags as below.
Always a blank field will be sent in the status tag.
<OrderID>1000</OrderID><ZipCode>07666</ZipCode>
<Status></Status>
Vendor will send back a file in response which looks like below:
<OrderID>1000</OrderID><ZipCode>07666</ZipCode>
<Status>Accepted</Status>
The response will contain a status of the order.
How can i use the response XML to update "Status".
Thanks.
First, you don't want to update your audit log with new data. That invalidates the purpose of the log.
Second, are you updating XML or a relational table?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply