sqlserver xml查询

declare @UsersList Xml
set @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)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!