Question
· Aug 22, 2022

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

Discussion (0)1
Log in or sign up to continue