XML ,query 和 value是区分大小写 的

DECLARE @x xml
SET @x='
<root>
  <ShopAccount>
    <ActivityType>IA - PM Standing WO (for LPI report)</ActivityType>
    <ProjectNo>R</ProjectNo>
  </ShopAccount>
  <ShopAccount>
    <ActivityType>IX - PM Associated WO (for LPI report)</ActivityType>
    <ProjectNo>C</ProjectNo>
  </ShopAccount>
</root>'
SELECT
ROW_NUMBER() OVER (order by T.c.value('ActivityType[1]','nvarchar(255)')) AS ROWID,
T.c.value('ActivityType[1]','nvarchar(255)') AS ActivityType
,T.c.value('ProjectNo[1]','nvarchar(255)') AS ProjectNo
from @x.nodes('/root/ShopAccount') T(c)
SELECT
ROW_NUMBER() OVER (order by T.c.query('ActivityType').value('ActivityType[1]','nvarchar(255)')) AS ROWID,
T.c.query('ActivityType').value('ActivityType[1]','nvarchar(255)') AS ActivityType
,T.c.query('ProjectNo').value('ProjectNo[1]','nvarchar(255)') AS ProjectNo
from @x.nodes('/root/ShopAccount') T(c)
declare @idoc int,@xmlPath nvarchar(200)
exec sp_xml_preparedocument @idoc OUTPUT, @x
set @xmlPath='/root/ShopAccount'
select
 ROW_NUMBER() OVER (order by ActivityType) AS ROWID,
 ActivityType,
 ProjectNo
from OPENXML (@idoc, @xmlPath)
WITH(ActivityType nvarchar(max) 'ActivityType',
  ProjectNo NVARCHAR(20) 'ProjectNo')
exec sp_xml_removedocument @idoc