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
<reqSQL xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:s="http://www.w3.org/2001/XMLSchema">
<SQLScript>
{ call dbo.PopulateDataTable(?,?,?,?,?,?,?)}
</SQLScript><lstInputParams><lstInputParamsItem>
test
</lstInputParamsItem><lstInputParamsItem>
666666
</lstInputParamsItem><lstInputParamsItem>
555555
</lstInputParamsItem><lstInputParamsItem>
<![CDATA[<TESTxmlns='http://tempuri.org/ICMPDSv3_1.xsd'><ADMISSION><identifier>ZX000</identifier><ACON>U</ACON><ACSD>0</ACSD><WKG>80</WKG><WKGEST>1</WKGEST></ADMISSION></TEST>]]>
</lstInputParamsItem><lstInputParamsItem>
S
</lstInputParamsItem></lstInputParams><numberofparams>
5
</numberofparams>
</reqSQL>
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