問題描述
我將 XML 負載存儲在數據類型為XML"的 SQL Server 表中.我收到的數據有一個包含在 CDATA 塊中的部分.下面是一個例子:
I am storing an XML payload in a SQL Server table that has a datatype of 'XML'. The data I am receiving has a section that is enclosed in a CDATA block. Here is an example:
<event>
<projectId>123456</projectId>
<eventTs>2018-01-04T13:07:23</eventTs>
<eventData>
<![CDATA[
<company>
<companyId>849</companyId>
<companyName>My Company Name</companyName>
<activeFlag>Y</activeFlag>
<timestamp>27-JUL-17</timestamp>
</company>
]]>
</eventData>
</event>
當這些數據出現在我的表中具有XML"數據類型的字段中時，CDATA"塊被剝離，但隨后所有的<"和 ">" 字符被轉義.由于這些字符已被轉義，因此對該數據字段的 XPATH 查詢不再有效.除了必須在插入/轉換為 XML 數據類型之前去掉 CDATA 塊之外，還有什么辦法可以解決這種行為?
when this data lands in my table in the field that has a data type of 'XML' the 'CDATA' block is stripped out but then all of the "<" and ">" characters are escaped. Since those characters are escaped, XPATH queries on that data field no longer work. Is there any way around this behavior short of having to strip out the CDATA block before it is inserted/converted to an XML data type?
這是插入到 XML 數據類型字段后的數據:
This is what the data looks like after being inserted into the XML datatype field:
<event>
<projectId>123456</projectId>
<eventTs>2018-01-04T13:07:23</eventTs>
<eventData>
<company>
<companyId>849</companyId>
<companyName>My Company Name</companyName>
<activeFlag>Y</activeFlag>
<timestamp>27-JUL-17</timestamp>
</company>
</eventData>
</event>
推薦答案
在 CDATA 部分中存儲 XML 是一種非常糟糕的方法.此塊中的所有內容都只是文本.此特殊文本看起來像一個 XML，但您無法查詢此 XML 以返回
It is a very bad approach to store XML within a CDATA section. Everything within this block is just text. This special text looks like an XML, but you cannot query this XML to return the <companyName>.
試試這個:
DECLARE @xml XML=
N'<event>
<projectId>123456</projectId>
<eventTs>2018-01-04T13:07:23</eventTs>
<eventData>
<![CDATA[
<company>
<companyId>849</companyId>
<companyName>My Company Name</companyName>
<activeFlag>Y</activeFlag>
<timestamp>27-JUL-17</timestamp>
</company>
]]>
</eventData>
</event>';
SQL Server 的開發人員甚至決定不再支持 CDATA.它會被隱含地帶走，而它的內容仍然被正確地轉義.但是你可以毫無問題地閱讀內容:
SQL Server's developer decided not even to support CDATA anymore. It will implicitly be taken away, while its content remains properly escaped. But you can read the content without problems:
SELECT @xml.value('(/event/eventData)[1]','nvarchar(max)');
重點是:這個結果看起來像一個 XML，但是 - 為了使用它像一個 XML - 它必須被強制轉換.
The point is: This result looks like an XML, but - in order to use it like an XML - it must be casted.
你可以這樣做來解決這個問題:
This you could do to solve this:
DECLARE @innerXML XML=(SELECT CAST('<eventData>' + @xml.value('(/event/eventData)[1]','nvarchar(max)') + '</eventData>' AS XML));
SET @xml.modify('delete /event/eventData[1]');
SET @xml.modify('insert sql:variable("@innerXML") as last into /event[1]');
SELECT @xml;
簡單易行:
只要傳入的 XML 是一個字符串(在您嘗試將其轉換為 XML 之前)，您就可以丟棄 CDATA 標記:
DECLARE @xmlString NVARCHAR(MAX)=
N'<event>
<projectId>123456</projectId>
<eventTs>2018-01-04T13:07:23</eventTs>
<eventData>
<![CDATA[
<company>
<companyId>849</companyId>
<companyName>My Company Name</companyName>
<activeFlag>Y</activeFlag>
<timestamp>27-JUL-17</timestamp>
</company>
]]>
</eventData>
</event>';
SELECT CAST(REPLACE(REPLACE(@xmlString,' <![CDATA[',''),']]>','') AS XML)
這篇關于帶有 CDATA 塊的 SQL Server XML 數據的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！