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
