有XML如下:
CREATE TABLE #a
(params XML)
INSERT INTO #a
( params )
VALUES ( '<groupchkorderinforeq>
<msgbody xmlns="http://www.xxxx.net/ngcrm/">
<custinfo>
<groupsubsinfo>
<productcode>202014111101</productcode>
<member>
<opttype>0</opttype>
<membermobile>13920130005</membermobile>
<membernum />
<membername>name0005</membername>
<memtype />
<prodchinfolist>
<prodchinfo>
<optype>0</optype>
<prodid>prod.10000001000884</prodid>
<payflag>0</payflag>
<efftype>0</efftype>
</prodchinfo>
</prodchinfolist>
</member>
</groupsubsinfo>
</custinfo>
</msgbody>
</groupchkorderinforeq>'
)
msgbody节点声名了命名空间,用平常的查询语句是查不到数据的
SELECT params.value('(/groupchkorderinforeq/msgbody/custinfo/groupsubsinfo/member/membermobile)[1]','nvarchar(1000)')
FROM #a
这时候我们要先用到WITH xmlnamespaces语句先声名一个命名空间,才能取到命名空间节点的值:
;WITH xmlnamespaces(
'http://www.xxxx.net/ngcrm/' as ns
)
SELECT params,params.value('(/groupchkorderinforeq/ns:msgbody/ns:custinfo/ns:groupsubsinfo/ns:member/ns:membermobile)[1]','nvarchar(1000)')
FROM #a