July 9, 2008 at 8:54 am
In the below test procedure, I have defined CustomerID as VARCHAR(10). If the XML passed to the proc contains 15 characters for CustomerID, the trailing 5 characters are trimmed and only the first 10 are returned. I am unable to predict the size of the values being passed in the XML and plan to eventually store the data in a table.
My preferred behavior for this process would have the procedure error if the XML data exceeds the defined length and not trim the extra characters.
Has anyone seen this and/or have any recommendations?
create PROCEDURE usp_xmltest
( @xml XML
)
AS
BEGIN
DECLARE @Pointer INT
EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@xml
SELECT
ProductID,
Price,
SaleDate,
SaleBatchID,
CustomerID
FROM OPENXML (@Pointer,'/ShoppingCart/Purchase', 1)
WITH (
ProductID INT,
Price MONEY,
SaleDate SMALLDATETIME,
SaleBatchID INT,
CustomerID varchar(10)
)
EXEC sp_xml_removedocument @Pointer
END
July 9, 2008 at 11:27 am
Hi Daniel
Are you trying to achieve data accuracy or an error handling mechanism?
Won't it be enough if you defined your field as VARCHAR(MAX)? You should enfornce length rules from within the user interface. SQL should be handling the data correctly. I think that 1000 or even 100 chars are enough. setting VARCHAR to such large values should do, unless someone is trying to inject SQL
Tal
July 9, 2008 at 12:22 pm
I am actually trying to achieve both and thought of extending the field definitions but would prefer to explore other options.
I saw that in Sybase there is STRING_RTRUNCATION setting that determines whether an error is raised when an INSERT or UPDATE truncates a string. I have not found anything like this in SQL.
July 9, 2008 at 1:28 pm
OK, then you may try one of the two:
1. - Raising the limit of the SQL to varchar(1000). After opening the XML test the DATALENGTH of the XML node and see if it's bigger than your limit (10) and raise an error
2. - If data needs to be saved in a table, so try raising the limit as in section 1 but putting the limit in the table. inserting longer data will raise an error
Tal
July 9, 2008 at 3:38 pm
Thank you for the suggestions. I had just finished coding and sanity testing your second suggestion when I got your reply. Your first option adds additional overhead to a process that is already flirting with becoming an environment bottleneck.
I am still holding out that there is another way to address this but deadlines may be limiting my choices.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply