如何在sql server中使用特殊字符解析xml ?

I am getting following error when i add < in my xml,

当我在我的xml中添加<，

Msg 9455, Level 16, State 1, Line 6 XML parsing: line 4, character 14, illegal qualified name character

Msg 9455，第16级，状态1，第6行XML解析:第4行，字符14，非法限定名字符。

How can i parse xml with these type of special characters?

如何用这些特殊字符解析xml ?

DECLARE @MyXML XML
SET @MyXML = '<SampleXML>
<Colors>
<Color1>W < hite</Color1>
<Color2>Blue</Color2>
<Color3>Black</Color3>
<Color4 Special="Light">Green</Color4>
<Color5>Red</Color5>
</Colors>
<Fruits>
<Fruits1>Apple</Fruits1>
<Fruits2>Pineapple</Fruits2>
<Fruits3>Grapes</Fruits3>
<Fruits4>Melon</Fruits4>
</Fruits>
</SampleXML>'

SELECT
a.b.value('Colors[1]/Color1[1]','varchar(10)') AS Color1,
a.b.value('Colors[1]/Color2[1]','varchar(10)') AS Color2,
a.b.value('Colors[1]/Color3[1]','varchar(10)') AS Color3,
a.b.value('Colors[1]/Color4[1]/@Special','varchar(10)')+' '+
+a.b.value('Colors[1]/Color4[1]','varchar(10)') AS Color4,
a.b.value('Colors[1]/Color5[1]','varchar(10)') AS Color5,
a.b.value('Fruits[1]/Fruits1[1]','varchar(10)') AS Fruits1,
a.b.value('Fruits[1]/Fruits2[1]','varchar(10)') AS Fruits2,
a.b.value('Fruits[1]/Fruits3[1]','varchar(10)') AS Fruits3,
a.b.value('Fruits[1]/Fruits4[1]','varchar(10)') AS Fruits4
FROM @MyXML.nodes('SampleXML') a(b)

3 个解决方案

#1

Invalid special characters & its substitute in xml

无效的特殊字符&以xml替代。

& - &
& -,
< - <
< - & lt;
> - >
>——比;
" - "
”——“;
' - '
”——& # 39;

#2

< needs to be specified as < in the XML

<需要指定为<在xml< p>

<SampleXML>
<Colors>
<Color1>W &lt; hite</Color1>
<Color2>Blue</Color2>
<Color3>Black</Color3>
<Color4 Special="Light">Green</Color4>
<Color5>Red</Color5>
</Colors>
<Fruits>
<Fruits1>Apple</Fruits1>
<Fruits2>Pineapple</Fruits2>
<Fruits3>Grapes</Fruits3>
<Fruits4>Melon</Fruits4>
</Fruits>
</SampleXML>

Update:

更新:

The characters you need to escape in node values are < => < and & => &.
In attribute values you also need to escape " => " if you use " around your attribute values.

在节点值中需要转义的字符是< => <和& = >,。在属性值中，您还需要摆脱“=>”;如果您使用“围绕您的属性值”。

This is a valid XML:

这是一个有效的XML:

<root>
  <item> &lt; > &amp; ' "</item>
  <item att=" &lt; > &amp; ' &quot;" />
</root>

Try it in a query:

在查询中尝试:

declare @xml xml =
'
<root>
  <item> &lt; > &amp; '' "</item>
  <item att=" &lt; > &amp; '' &quot;" />
</root>
'

select @xml.value('(root/item)[1]', 'varchar(20)') as NodeValue,
       @xml.value('(root/item/@att)[1]', 'varchar(20)') as AttValue

Result:

结果:

NodeValue            AttValue
-------------------- --------------------
 < > & ' "            < > & ' "

#3

You need to ensure the XML is valid, so you need to make sure any special characters are encoded.

您需要确保XML是有效的，因此您需要确保对任何特殊字符进行编码。

e.g.

如。

DECLARE @MyXML XML
SET @MyXML = '<SampleXML>
<Colors>
<Color1>W &lt; hite</Color1>
<Color2>Blue</Color2>
<Color3>Black</Color3>
<Color4 Special="Light">Green</Color4>
<Color5>Red</Color5>
</Colors>
<Fruits>
<Fruits1>Apple</Fruits1>
<Fruits2>Pineapple</Fruits2>
<Fruits3>Grapes</Fruits3>
<Fruits4>Melon</Fruits4>
</Fruits>
</SampleXML>'

#1