今天第一次尝试用xpath去搜索xml字符串,把自己写的sql帖在下面,以后参考
1
WITH
XMLNAMESPACES (
2 ' http://www.gamingstandards.com/s2s/schemas/v1.2.6/ ' AS s2s,
3 ' http://s2s.igt.com/player-ext1/v1.0.0 ' as p1)
4 select cast (body as xml).value( ' (/s2s:s2sMessage/s2s:s2sBody/s2s:player/s2s:playerInfo/@p1:playerId)[1] ' , ' varchar(50) ' ) from dbo.s2sOutBound_Complete
5 where cast (body as xml).exist( ' /s2s:s2sMessage/s2s:s2sBody/s2s:player/s2s:playerInfo ' ) = 1
2 ' http://www.gamingstandards.com/s2s/schemas/v1.2.6/ ' AS s2s,
3 ' http://s2s.igt.com/player-ext1/v1.0.0 ' as p1)
4 select cast (body as xml).value( ' (/s2s:s2sMessage/s2s:s2sBody/s2s:player/s2s:playerInfo/@p1:playerId)[1] ' , ' varchar(50) ' ) from dbo.s2sOutBound_Complete
5 where cast (body as xml).exist( ' /s2s:s2sMessage/s2s:s2sBody/s2s:player/s2s:playerInfo ' ) = 1