XML Validation WITH SQL

Question

Hello, I have created a stored procedure that will return the error of XML schema validation. But ERROR_MESSAGE doesn't display the full message of the error. If I replace ERROR_MESSAGE with THROW then I get the full message but I want to store the error into a string variable and I need full error.

Answer 1

Hi @ΜΑΡΙΑ ΝΙΚΟΛΟΠΟΥΛΟΥ ,

Welcome to Microsoft Q&A!

We recommend that you post your procedure, XML Schema and error details if possible, enough to illustrate all angles of the problem. We also need to see the expected result.

but I want to store the error into a string variable and I need full error.

You could refer below:

DECLARE @err_msg AS NVARCHAR(MAX);  
SET @err_msg = ERROR_MESSAGE();  
select @err_msg  

Or using BEGIN TRY/CATCH and RAISERROR as below:

DECLARE  
  @ErrorMessage   nvarchar(max)  
 ,@ErrorSeverity  tinyint  
 ,@ErrorState     tinyint  
  
BEGIN TRY  
--type your code here  
END TRY  
BEGIN CATCH  
    SET @ErrorMessage  = ERROR_MESSAGE()  
    SET @ErrorSeverity = ERROR_SEVERITY()  
    SET @ErrorState    = ERROR_STATE()  
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)  
    BREAK  
END CATCH  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Testing XML for being well-formed and valid was designed in a way, so that you can offload it.

Thus the questions are:

Why doing it in T-SQL?
Why storing invalid or malformed XML?

Answer 3

First of all, thank you all for your answer.

I have create store procedure:

ALTER PROCEDURE [dbo].[spValidateXMLInvDoc] (@UnId SYSNAME, @ComId SYSNAME, @stringXML nvarchar(max) output)

AS BEGIN

SET NOCOUNT ON

DECLARE @x XML(dbo.invoicesDoc)
DECLARE
@ErrorMessage nvarchar(max)
,@ErrorSeverity tinyint
,@ErrorState tinyint

BEGIN TRY
;WITH XMLNAMESPACES (
DEFAULT 'http://www.aade.gr/myDATA/invoice/v1.0',
'https://www.aade.gr/myDATA/incomeClassificaton/v1.0' as icls,
'https://www.aade.gr/myDATA/expensesClassificaton/v1.0' as ecls,
'http://www.w3.org/2001/XMLSchema' as xsi)
SELECT @x = (SELECT (
SELECT (SELECT invoiceHeader.[xmlInfo])
FROM dbo.invoiceHeader
WHERE invoiceHeader.unid = @UnId
AND invoiceHeader.ComId = @ComId
FOR XML PATH (''), TYPE
)
FOR XML PATH('InvoicesDoc'), TYPE);
END TRY
--SET @stringXML = CONVERT(nvarchar(max), @x, 1);
BEGIN CATCH
--THROW;
SET @stringXML = ERROR_MESSAGE();
PRINT ERROR_MESSAGE();
PRINT @stringXML;
--SET @ErrorMessage = ERROR_MESSAGE()
--SET @ErrorSeverity = ERROR_SEVERITY()
--SET @ErrorState = ERROR_STATE()
--RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH

RETURN;
END

but the output of store procedure returns:
XML Validation: Invalid content. Expected element(s): '{http://www.aade.gr/myDATA/invoice/v1.0}movePurpose'. Found: element '{ht

and I want to return the full message of ERROR_MESSAGE that is:
XML Validation: Invalid content. Expected element(s): '{http://www.aade.gr/myDATA/invoice/v1.0}movePurpose'. Found: element '{http://www.aade.gr/myDATA/invoice/v1.0}movePurspose' instead. Location: /:InvoicesDoc[1]/:invoice[1]/:invoiceHeader[1]/:movePurspose[1].

Answer 4

Aha, the problem has nothing to do with validating XML per se.

You declare the variable for your created XML as

DECLARE @x XML(dbo.invoicesDoc)

Thus your FOR XML PATH() calls must return a valid document. The error you're getting, tells you that you create an XML not matching the XML schema in dbo.invoicesDoc.

So look up that schema and create your XML accordingly.

Answer 5

Sorry ok, I found my mistake, I have declared the variable as sysname. Thank you very much.