Validate XML data values before type conversion
-
Scenario on SS2K (please don't make any suggestions involving "go to/in SS2005") as it ain't gonna happen for many months to come
):An SP's ntext parameter receives an XML doc comprising a variable number of Shipments for an Order. We have no DTD or XSL(T) to describe the document as it's a message typically from an ASP.Net page requiring an SP to log the Shipments all at once (saves network traffic and they belong in a single transaction). "Typically" is the watchword - requests can come from other SP's and eventually via an Object Broker (when we get to SS2K5).
Ideally, the UI will never pass bad data
. However, to simply trust that all is Ok and blindly fire off an OPENXML into typed columns invites an exception (too large an integer, overflow of string data, bad date, to name just three...). That exception cannot be trapped in SS2K and .Net gets it - raw (nasty!).Having Googled for a day and scoured sqlservercentral, I've found nothing that mentions validating elements prior to loading into the typed columns.
Does anyone know of any decent links that deal with this subject?
As a stopgap (to "protect" the db and not be perceived as a lazy developer) I've thrown together a few lines that OPENXML into a table-variable's nvarchar columns provided the respective element data lengths don't exceed the target string fields or the max length that a CONVERT will handle to get to a date or number. An UPDATE to the TV then validates each column, setting a corresponding error-literal if an error exists. If all error-literals are NULL, another OPENXML reads the document into another table-variable having typed columns and initiates the Shipment logging. It looks hokey but works very well. If there are built-in (SS2000) features I can leverage, I'd rather use them... Do enlighten if you know of them, please...
Thanks in advance.
-
I'm not aware of anything in SS2000 for that. I usually validate in the application while I'm building the XML doc before passing it to the database.
--Andrew
-
Yes, the UI should validate and pass a valid doc. In this case, the SP will use another well-used SP to handle each Shipment with the initial invocation originating from the UI, but as we have "batch" processing too from outside vendors, validation in the SP seems prudent.
Anyone know of any links to validate XML within SS2000? If not, it looks like I'm on my own (which could be fun
)In case it helps, I've added a DTD and a sample of what it is I'm needing to validate, where the ID's are integers, quantity is int and the ShippedDate is a valid date (with or without the time portion):
<?xml version="1.0"?> <!DOCTYPE Order [ <!ELEMENT Order (OrderDetail+)> <!ELEMENT OrderDetail (Shipment+)> <!ATTLIST OrderDetail OrderDetailID CDATA #REQUIRED> <!ELEMENT Shipment (QuantityShipped,ParcelCode,TrackingNumber,ShippedDate)> <!ELEMENT QuantityShipped (CDATA)> <!ELEMENT ParcelCode (CDATA)> <!ELEMENT TrackingNumber (CDATA)> <!ELEMENT ShippedDate (CDATA)> ]> <Order> <OrderDetail OrderDetailID="12345"> <Shipment> <QuantityShipped>1</QuantityShipped> <ParcelCode><![CDATA[P12311]]></ParcelCode> <TrackingNumber>1Z1234567890</TrackingNumber> <ShippedDate><![CDATA[12-31-2006]]></ShippedDate> </Shipment> <Shipment> <QuantityShipped>1</QuantityShipped> <ParcelCode><![CDATA[P12322]]></ParcelCode> <TrackingNumber>1Z1234567891</TrackingNumber> <ShippedDate><![CDATA[12-31-2006]]></ShippedDate> </Shipment> <Shipment> <QuantityShipped>1</QuantityShipped> <ParcelCode><![CDATA[P12333]]></ParcelCode> <TrackingNumber>1Z1234567892</TrackingNumber> <ShippedDate><![CDATA[12-31-2006]]></ShippedDate> </Shipment> </OrderDetail> <OrderDetail OrderDetailID="22222"> <Shipment> <QuantityShipped>1</QuantityShipped> <ParcelCode><![CDATA[null]]></ParcelCode> <TrackingNumber>1Z23456789012</TrackingNumber> <ShippedDate><![CDATA[12-31-2006]]></ShippedDate> </Shipment> <Shipment> <QuantityShipped>2</QuantityShipped> <ParcelCode><![CDATA[null]]></ParcelCode> <TrackingNumber><![CDATA[&]]></TrackingNumber> <ShippedDate><![CDATA[12-32-2999]]></ShippedDate> </Shipment> </OrderDetail> </Order>
-
The best I have come up with is to create a schema for the data and run the document through an external validating parser, but this still has to happen outside the database before calling the procedure - not exactly what you are looking for and perhaps not possible with your batch workflow. I did see an article about data validation in SQL server, but it is still not an internal solution and looks even more complicated that what you are already doing.
I would be interested to see if anyone else has a better solution.
--Andrew
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply