XML查询示例

Declare @xmlDoc xml

Set @xmlDoc = '
<Root>
<EnterInfo>
<Item IName="name">
<IMeaning>姓名</IMeaning>
<IValue><![CDATA[啊啊啊啊啊<br>&"/userfiles/image/big20110825104441.jpg]]></IValue>
</Item>
<Item IName="sex" IMeaning="性别:男=1；女=2；" IValue="2" />
<Item IName="mobile" IMeaning="手机" IValue="765435467467" />
<Item IName="co" IMeaning="单位" IValue="1" />
<Item IName="remark" IMeaning="备注" IValue="aaa" />
<Item IName="test1">aaaaaaaaaaaa</Item>
<Item IName="test">
<![CDATA[<br>&"/userfiles/image/big20110825104441.jpg]]>
<![CDATA[22222]]>
</Item>
</EnterInfo>
</Root>
'

Select
@xmlDoc.value('(/Root/EnterInfo/Item[@IName="name"])[1]/@IValue', 'nvarchar(64)')
,@xmlDoc.value('(/Root/EnterInfo/Item[@IName="sex"])[1]/@IValue', 'nvarchar(64)')
,@xmlDoc.value('(/Root/EnterInfo/Item[@IName="mobile"])[1]/@IValue', 'nvarchar(64)')
,@xmlDoc.value('(/Root/EnterInfo/Item[@IName="co"])[1]/@IValue', 'nvarchar(64)')
,@xmlDoc.value('(/Root/EnterInfo/Item[@IName="remark"])[1]/@IValue', 'nvarchar(64)')
,@xmlDoc.value('(/Root/EnterInfo/Item[@IName="test1"])[1]', 'nvarchar(1024)')
,@xmlDoc.value('(/Root/EnterInfo/Item[@IName="test"])[1]', 'nvarchar(1024)')
,@xmlDoc.value('(/Root/EnterInfo/Item[@IName="name"])[1]/IValue[1]', 'nvarchar(1024)')