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   ,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)