問(wèn)題描述
我將 XML 負(fù)載存儲(chǔ)在數(shù)據(jù)類(lèi)型為XML"的 SQL Server 表中.我收到的數(shù)據(jù)有一個(gè)包含在 CDATA 塊中的部分.下面是一個(gè)例子:
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>
當(dāng)這些數(shù)據(jù)出現(xiàn)在我的表中具有XML"數(shù)據(jù)類(lèi)型的字段中時(shí)，CDATA"塊被剝離，但隨后所有的<"和 ">" 字符被轉(zhuǎn)義.由于這些字符已被轉(zhuǎn)義，因此對(duì)該數(shù)據(jù)字段的 XPATH 查詢(xún)不再有效.除了必須在插入/轉(zhuǎn)換為 XML 數(shù)據(jù)類(lèi)型之前去掉 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 數(shù)據(jù)類(lèi)型字段后的數(shù)據(jù):
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 部分中存儲(chǔ) XML 是一種非常糟糕的方法.此塊中的所有內(nèi)容都只是文本.此特殊文本看起來(lái)像一個(gè) XML，但您無(wú)法查詢(xún)此 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>.
試試這個(gè):
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 的開(kāi)發(fā)人員甚至決定不再支持 CDATA.它會(huì)被隱含地帶走，而它的內(nèi)容仍然被正確地轉(zhuǎn)義.但是你可以毫無(wú)問(wèn)題地閱讀內(nèi)容:
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)');
重點(diǎn)是:這個(gè)結(jié)果看起來(lái)像一個(gè) XML，但是 - 為了使用它像一個(gè) XML - 它必須被強(qiáng)制轉(zhuǎn)換.
The point is: This result looks like an XML, but - in order to use it like an XML - it must be casted.
你可以這樣做來(lái)解決這個(gè)問(wèn)題:
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;
簡(jiǎn)單易行:
只要傳入的 XML 是一個(gè)字符串(在您嘗試將其轉(zhuǎn)換為 XML 之前)，您就可以丟棄 CDATA 標(biāo)記:
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)
這篇關(guān)于帶有 CDATA 塊的 SQL Server XML 數(shù)據(jù)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！