在SQL server中转换为具有特殊字符名的XML

There is a XML in the below format present in a column of datatype VARCHAR(MAX) in sql table

在sql表中，datatype VARCHAR(MAX)列中显示了以下格式的XML

<?xml version="1.0" encoding="UTF-8"?>
<APIDATA xmlns="mynamespace"> 
<TRADE Action="Insert" CompanyID="33" Trader="Aleš Holubec" 
</TRADE>
</APIDATA>

I need to fetch attribute values of Action and CompanyID . i.e, Insert and 33

我需要获取Action的属性值和CompanyID。我。e、插入和33

In SQL, i used the below query

在SQL中，我使用了下面的查询

;WITH XMLNAMESPACES(DEFAULT 'mynamespace')

SELECT CONVERT(XML,column_name).value ('(APIDATA/TRADE/@Action)[1]', 'varchar(100)')

But i get the below error

但我得到了下面的误差

XML parsing: line 1, character 537, illegal xml character

The reason is Aleš Holubec is the name of the trader in the XML which we cannot change. Please help how to resolve this

原因是AlešHolubec交易员在XML的名称,我们无法改变。请帮忙解决这个问题

2 个解决方案

#1

You can fix this by converting your string to nvarchar(max) and then replace the encoding to UTF-16.

可以通过将字符串转换为nvarchar(max)，然后将编码替换为UTF-16来解决这个问题。

;WITH XMLNAMESPACES(DEFAULT 'mynamespace')
SELECT CONVERT(XML, replace(cast(column_name as nvarchar(max)), '<?xml version="1.0" encoding="UTF-8"?>', '<?xml version="1.0" encoding="UTF-16"?>')).value ('(APIDATA/TRADE/@Action)[1]', 'varchar(100)')

Or you can just remove the XML declaration.

或者您可以删除XML声明。

;WITH XMLNAMESPACES(DEFAULT 'mynamespace')
SELECT CONVERT(XML, replace(column_name, '<?xml version="1.0" encoding="UTF-8"?>', '')).value ('(APIDATA/TRADE/@Action)[1]', 'varchar(100)')

#2

I ran into the same issue with special characters and XML. Below is an example of what I did to resolve the problem with working code that you will need to modify for your DB.

我遇到了特殊字符和XML的相同问题。下面是我为解决工作代码问题所做的一个示例，您需要为您的DB修改这些代码。

The only thing I needed C# to handle for me was the single quote. C# will need to change the single quote to ' before sending it as a CSV list, if not it will fail.

我唯一需要c#来处理的就是那句引用的话。c#需要在发送CSV列表之前将单引号更改为'，否则将失败。

Hope this helps..

希望这可以帮助. .

/**************************************************************************************
 **************************************************************************************
 ***    
 ***    This is to demonstrate how to use XML in SQL statements.
 ***    C# must send the the list of items in comma seperated values and 
 ***    replace the character Single Quote with ' or the SQL statement 
 ***    will fail.  This is the only character that SQL can not handle when sent as a
 ***    string.
 ***    
 ***    The steps below will explain each part and how it handles special 
 ***    characters in XML and turns them into a temp table.
 ***
 ***
 **************************************************************************************
 **************************************************************************************/

SET ARITHABORT ON

DECLARE @CUST_REF3 AS XML 
DECLARE @CSharpString AS NVARCHAR(MAX)

    --This is used for when the CSV string is passed in normally.
SET @CSharpString = ISNULL('{0}', '')
    --Example of when the CSV string is passed in and it having a special characters.
SET @CSharpString = 'SHOP A, LA WHEEL & BRAKE SHOP, SHOP's B'
    --Example of when nothing is passed in from C#
    --SET @CSharpString = ''

--Replaces any special character for preperation of XML formating
SET @CSharpString = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CSharpString,'&', '&amp;'),'<', '&lt;'),'>', '&gt;'),'"', '&quot;'),'''', '&apos;')

-- Turn the @CSharpString (CSV) into XML
SET @CUST_REF3 = CAST('<A>'+ NULLIF(REPLACE(@CSharpString, N',', '</A><A>'), '') + '</A>' AS XML)

--Display the converted XML list
SELECT @CUST_REF3

--Converts the XML nodes into a temp table.  Make sure the data type is set correctly for the data being received.
SELECT dbo.Trim(CUST_REF3.value('.','nchar(22)')) AS [CUST_REF3] 
INTO #XML_List
FROM @CUST_REF3.nodes('/A') AS x(CUST_REF3)

--Display what Converted list
SELECT * FROM #XML_List

--example of output from the C# String
SELECT USER_DOC, CUST_REF3 
  FROM dbo.WO_HDR 
 WHERE 
        --This matches the list of items sent from C#
      ((SELECT TOP(1) x.CUST_REF3 
          FROM #XML_List x 
         WHERE x.CUST_REF3 = dbo.WO_HDR.CUST_REF3) IS NOT NULL OR

         --If the user did not select anything, do not filter
         @CSharpString = '')

--Dispose of temp table
IF OBJECT_ID('tempdb..#XML_List') IS NOT NULL                   
DROP TABLE #XML_List

/**********************************************************************************************************************************************************
--Summerized code to use:
        SET ARITHABORT ON
        DECLARE @CUST_REF3 AS XML 
        DECLARE @CSharpString AS NVARCHAR(MAX)

        SET @CSharpString = ISNULL('{0}', '')
        SET @CSharpString = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CSharpString,'&', '&amp;'),'<', '&lt;'),'>', '&gt;'),'"', '&quot;'),'''', '&apos;')
        SET @CUST_REF3 = CAST('<A>'+ NULLIF(REPLACE(@CSharpString, N',', '</A><A>'), '') + '</A>' AS XML)

        SELECT dbo.Trim(CUST_REF3.value('.','nchar(22)')) AS [CUST_REF3] INTO #XML_List FROM @CUST_REF3.nodes('/A') AS x(CUST_REF3)

        --Add this scring to every WHERE statment you want this filter to be used.
        --((SELECT TOP(1) x.CUST_REF3 FROM #XML_List x WHERE x.CUST_REF3 = dbo.WO_HDR.CUST_REF3) IS NOT NULL OR @CSharpString = '')

        IF OBJECT_ID('tempdb..#XML_List') IS NOT NULL DROP TABLE #XML_List
 **********************************************************************************************************************************************************/