Input XML into SQL server table
Hi,
I am trying to input an entire xml into a sql server table. I am doing this via an operation in Ensemble.
I keep getting the following error
[SQL Server]XML parsing: line 0, character 0, unrecognized input signature
I have setup the table with a column parameter XML.
Here is the request object that is being sent to the store procedure
{ call dbo.PopulateDataTable(?,?,?,?,?,?,?)}
test
666666
555555
<![CDATA[<TESTxmlns='http://tempuri.org/ICMPDSv3_1.xsd'><ADMISSION><identifier>ZX000</identi…;
S
5
This is my store procedure in SQL
USE [test]
GO
/****** Object: StoredProcedure [dbo].[PopulateDataTable] Script Date: 08/22/2022 08:39:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[PopulateDataTable]
-- Add the parameters for the stored procedure here
@HospitalNumber VARCHAR(20),
@CaseID VARCHAR(20),
@InpatientSpellID VARCHAR(20),
@XML XML,
@ProcessFlag VARCHAR(5),
@LogComment VARCHAR (100) OUTPUT,
@Alert INT OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRAN
--SET OUTPUT DEFAULT VALUES
SET @LogComment = 'No Log Comment'
SET @Alert = 0
BEGIN
INSERT INTO dbo.GECHAR_ICU_Data (HospitalNumber,CaseNumber,InpatientSpellId,GECHAR_ICU_Data.XML,UserCreate,CreateDateTime,UserModifed,ModifiedDateTime,Processed)
VALUES (@HospitalNumber,@CaseID, @InpatientSpellID, @XML, 'Service' , GETDATE(), 'Service' , GETDATE(), @ProcessFlag)
SET @LogComment = 'Process Flag Updated'
END
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN 99
END
COMMIT TRAN
END
Thanks
Comments
The material I found suggests the issue is likely with how the XML parameter is being bound when calling SQL Server from EnsLib.SQL.OutboundAdapter, rather than with the stored procedure itself. In a related Ensemble/SQL Server parameterized call, passing complex text worked when the SQL parameter type was treated as VARCHAR(8000), but failed for NVARCHAR(MAX) and NVARCHAR(8000). A suggested workaround was to call the procedure using the parameter-array form and explicitly set the parameter SQL type to VARCHAR, for example: Set tParams(1)=xmlContent and Set tParams(1,"SqlType")=$$$SqlVarchar, then use ExecuteProcedureParmArray(...). [1][2]
Your XML payload also appears malformed in the example you posted: the CDATA contains <TESTxmlns='http://tempuri.org/ICMPDSv3_1.xsd'>, which has no space between the element name and the xmlns attribute. That means the opening tag shown is not well-formed XML as written. [3]
The procedure definition you posted has 7 parameters total, with 5 input parameters and 2 output parameters (@LogComment and @Alert). Your request object shows numberofparams as 5 while the SQL call text is { call dbo.PopulateDataTable(?,?,?,?,?,?,?) }, so you should make sure the outbound call is supplying the parameters in a way that matches the procedure signature, especially if output parameters are involved. [3]
A practical adjustment based on the retrieved material is to:
- Ensure the XML string itself is well-formed.
- Pass the XML as a parameter array element rather than inline text.
- Explicitly set that parameter’s SQL type to
VARCHARin the adapter call. [3][1][2]
Example pattern from the retrieved discussion:
set tQuery="{ CALL dbo.SavePatientDetails (?) }"
set tParams=1
set tParams(1)=xmlContent
set tParams(1,"SqlType")=$$$SqlVarchar ; or 12 if macro does not resolve
set tSC = ..Adapter.ExecuteProcedureP...
If you continue using your current request wrapper, also verify that the parameter count and positions match the stored procedure definition exactly. [3]
Sources: