sqlserver xml查询

概述declare @UsersList Xmlset @UsersList='<department></department><Users><ID val="cc">PC00000000002615</ID><ID val="bb">PC00000000009999</ID></Users>' Select ID.value('.','nvarchar(40)') AS USERSID

declare @UsersList Xmlset @UsersList='<department></department><Users><ID val="cc">PC00000000002615</ID><ID val="bb">PC00000000009999</ID></Users>' Select ID.value('.','nvarchar(40)') AS USERSID,ID.value('./@val','nvarchar(40)')From @UsersList.nodes('/Users/ID') AS USERSID(ID)Select COUNT(ID.value('@val','nvarchar(40)'))From @UsersList.nodes('/Users/ID') AS USERSID(ID)Select COUNT(1)From @UsersList.nodes('/UseRSS/IDs') AS USERSID(ID)Select @UsersList.query('/Users/ID') --查询XML中Users节点下所有子节点是ID的串Select @UsersList.query('/Users/ID').value('.','NVarchar(40)') --查询XML中Users节点下所有子节点是ID的值Select @UsersList.query('/Users/ID[@val="cc"]') --查询XML中Users节点下所有子节点是ID，并且val属性是cc的节点Select @UsersList.query('/Users/ID[@val="cc"]').value('.','NVarchar(40)') --查询XML中Users节点下所有子节点是ID，并且val属性是cc的节点的值Select @UsersList.query('/Users/ID/text()[0]') --取Users节点下子节点是ID的值，顺序从1开始，不存在将以空串显示Select @UsersList.query('(/Users/ID/text())[1]')Select @UsersList.query('(/Users/ID/text())[2]')Select @UsersList.query('(/Users/ID/text())[3]') Select ID.value('.','nvarchar(40)') AS USERSID    --ID值相同 *将XML串转换成表形式显示*From @UsersList.nodes('/Users/ID') AS USERSID(ID)Select ID.value('.','nvarchar(40)') AS USERSID    --ID值相同只取属性是cc的记录行 *将XML串转换成表形式显示*From @UsersList.nodes('/Users/ID[@val="cc"]') AS USERSID(ID)Select ID2.value('.','nvarchar(40)') AS USERSID    --ID2相同From @UsersList.nodes('/Users/ID2') AS USERSID(ID2)Select ID2.value('.','nvarchar(40)') AS USERSID From @UsersList.nodes('/Users/ID') AS USERSID(ID2)

总结

以上是内存溢出为你收集整理的sqlserver xml查询全部内容，希望文章能够帮你解决sqlserver xml查询所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。