存储过程插入/更新语句在插入XML字符串时抛出错误

I have requirement to Insert or Update XML data column value with new XML string. I'm passing XML string as parameter in Store Procedure.

我要求使用新的XML字符串插入或更新XML数据列值。我在存储过程中将XML字符串作为参数传递。

I'm facing below Error Messages when execute it manually using "EXEC" command in SQL Management Studio.

在SQL Management Studio中使用“EXEC”命令手动执行时,我面临错误消息。

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'text'.
Msg 132, Level 15, State 1, Line 1
The label 'http' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 103, Level 15, State 4, Line 1
The identifier that starts with '><td>Are you deaf or do you have difficulty hearing?</td><td><content>No</content></td></tr></tbody></table></text></section></c' is too long. Maximum length is 128.
Msg 103, Level 15, State 4, Line 1
The identifier that starts with ' /><title>Plan of Care</title><text><paragraph>Not on file</paragraph></text></section></component><component><section><template' is too long. Maximum length is 128.
Msg 103, Level 15, State 4, Line 1
The identifier that starts with ' /></observation></entryRelationship></observation></entryRelationship></act></entry></section></component></structuredBody></co' is too long. Maximum length is 128.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ' /></observation></entryRelationship></observation></entryRelationship></act></entry></section></component></structuredBody></com'.

I have below EXEC command statement. You can get my XML string from this link. Save this file in your computer and open in any of the Editor. Passed this XML data as string in below SP as input parameter "@XMLData".

我有下面的EXEC命令声明。您可以从此链接获取我的XML字符串。将此文件保存在您的计算机中,然后在任何编辑器中打开。在下面的SP中将此XML数据作为字符串传递为输入参数“@XMLData”。

EXEC [InsertUpdateClinicalData] 2,'',1

Store Procedure:

ALTER PROCEDURE [dbo].[InsertUpdateClinicalData]
(
      @ID INT,
      @XMLData XML,
      @UserID INT
)
AS
BEGIN

IF(@ID IS NOT NULL AND @ID > 0)
    BEGIN
        UPDATE ClinicalData
            SET XMLData=@XMLData,
                ImportedDate=getdate()
            WHERE
                [ID]=@ID
    END
ELSE
    BEGIN
        INSERT INTO ClinicalData
            (
                XMLData,
                UserID,
                CreatedDate,
                ImportedDate
            )
        VALUES
            (
                @XMLData,
                @UserID,
                getdate(),
                getdate()
            )
    END

SELECT SCope_Identity();
END

I tried all the ways to solve this problem but failed every time.

我尝试了所有方法来解决这个问题,但每次都失败了。

3 个解决方案

#1

In your XML file ur using utf-8. you need to convert UTF-8 to UTF-16.Then try it will work.see image for updated xml

在您的XML文件中使用utf-8。你需要将UTF-8转换为UTF-16。然后尝试它将工作。查看更新的xml的图像

#2

EDIT: Try to read it like this

编辑:尝试这样读

DECLARE @xml XML=
(SELECT *  FROM OPENROWSET(BULK 'F:\Daten\XMLFile.xml',SINGLE_CLOB) AS x);
SELECT @xml;

It will be transfered to UTF-16 internally (this is the SQL Server's internal enconding of XML). The content of processing instructions is not validated. Might be that the stylesheet is not applied properly. But - to be honest - I think this will work actually...

它将在内部转移到UTF-16(这是SQL Server的内部enconding XML)。处理指令的内容未经过验证。可能是样式表未正确应用。但是 - 说实话 - 我认为这实际上会起作用......

I think I know the reason for your errors: You fill in the XML as a string like SET @myVariable= 'my XML here'. This breaks at the second line, because SQL Server takes the single quotes as end of the string. The first error you' ve got ("near text") comes from the second line:

我想我知道你的错误的原因:你把XML填充为像SET @ myVariable ='my XML here'这样的字符串。这在第二行中断,因为SQL Server将单引号作为字符串的结尾。你得到的第一个错误(“近文”)来自第二行:

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type='text/xsl' href='STYLE.XSL'?>
<ClinicalDocument xmlns="urn:hl7-org:v3">

Your stylesheet directive uses single quotation, the others use doubled.

您的stylesheet指令使用单引号,其他使用doubled。

After repairing this you are breaking again at

修复后,你再次破坏了

<td ID="height_5520578700">1.727 m (5' 8")</td>

... due to the ' after the 5.

...由于'在5之后。

The next problem might be here

下一个问题可能就在这里

<td ID="temp_5520578700">37.7 °C (99.8 °F)</td>

Replace the small circle with °

用°替换小圆圈

You must repair your XML first...

您必须先修复XML ...

#3

I found the solution of this type of error. I just removed below lines from XML string before I passes it to as Store-Procedure Parameter.

我找到了这种错误的解决方案。在将其传递给Store-Procedure Parameter之前,我刚从XML字符串中删除了以下行。

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type='text/xsl' href='STYLE.XSL'?>

Below is my C# code:

下面是我的C#代码:

lsXMLResponse = loXMLResponse.Content.Replace("<?xml version=\"1.0\" encoding=\"UTF-8\"?><?xml-stylesheet type='text/xsl' href='STYLE.XSL'?>", "");

#1