CREATE PROCEDURE XMLATTR$SCH_Int_COUNT_test @str    xml (DOCUMENT attrnum),
                                       @retdata  bit = 1,
                                       @tookms   int = NULL OUTPUT AS

DECLARE @start datetime2(3)
SELECT @start = sysdatetime()

DECLARE @cnt bigint
SELECT @cnt = SUM(len(word))
FROM   usrdictwords u
JOIN   @str.nodes('/Root[1]/Num') AS T(Item)
  ON   u.wordno = T.Item.value('@num[1]', 'int')

SELECT @tookms = datediff(ms, @start, sysdatetime());



go

CREATE PROCEDURE XMLATTR$SCH_Int_EXISTS_test @str    xml(DOCUMENT attrnum),
                                       @retdata  bit = 1,
                                       @tookms   int = NULL OUTPUT AS

DECLARE @start datetime2(3)
SELECT @start = sysdatetime()

INSERT #Int_JOIN (word)
SELECT u.word
FROM   usrdictwords u
WHERE  EXISTS (SELECT * FROM @str.nodes('/Root[1]/Num') AS T(Item)
  WHERE   u.wordno = T.Item.value('@num[1]', 'int'))

SELECT @tookms = datediff(ms, @start, sysdatetime());

IF @retdata = 1 SELECT word FROM #Int_JOIN

TRUNCATE TABLE #Int_JOIN

go

CREATE PROCEDURE XMLATTR$SCH_Int_JOIN_test @str    xml(DOCUMENT attrnum),
                                       @retdata  bit = 1,
                                       @tookms   int = NULL OUTPUT AS

DECLARE @start datetime2(3)
SELECT @start = sysdatetime()

INSERT #Int_JOIN (word)
SELECT u.word
FROM   usrdictwords u
JOIN   @str.nodes('/Root[1]/Num') AS T(Item)
  ON   u.wordno = T.Item.value('@num[1]', 'int')

SELECT @tookms = datediff(ms, @start, sysdatetime());

IF @retdata = 1 SELECT word FROM #Int_JOIN

TRUNCATE TABLE #Int_JOIN

go

CREATE PROCEDURE XMLATTR$SCH_Int_UNPACK_test @str    xml(DOCUMENT attrnum),
                                         @retdata  bit = 1,
                                         @tookms   int = NULL OUTPUT AS

DECLARE @start datetime2(3)
SELECT @start = sysdatetime()

INSERT #Int_UNPACK (number)
SELECT n = T.Item.value('@num[1]', 'int')
FROM   @str.nodes('/Root[1]/Num') AS T(Item)

SELECT @tookms = datediff(ms, @start, sysdatetime());

IF @retdata = 1 SELECT number FROM #Int_UNPACK

TRUNCATE TABLE #Int_UNPACK

go

CREATE PROCEDURE XMLATTR$SCH_Str_COUNT_test @str    xml(DOCUMENT attrstr),
                                       @retdata  bit = 1,
                                       @tookms   int = NULL OUTPUT AS

DECLARE @start datetime2(3)
SELECT @start = sysdatetime()

DECLARE @cnt bigint
SELECT @cnt = SUM(len(guid))
FROM   usrdictwords u
JOIN   @str.nodes('/Root[1]/Word') AS T(Item)
  ON   u.word = T.Item.value('@Item[1]', 'nvarchar(50)')

SELECT @tookms = datediff(ms, @start, sysdatetime());



go

CREATE PROCEDURE XMLATTR$SCH_Str_EXISTS_test @str    xml(DOCUMENT attrstr),
                                       @retdata  bit = 1,
                                       @tookms   int = NULL OUTPUT AS

DECLARE @start datetime2(3)
SELECT @start = sysdatetime()

INSERT #Str_JOIN (wordno, guid)
SELECT u.wordno, u.guid
FROM   usrdictwords u
WHERE  EXISTS (SELECT * FROM @str.nodes('/Root[1]/Word') AS T(Item)
  WHERE   u.word = T.Item.value('@Item[1]', 'nvarchar(50)'))

SELECT @tookms = datediff(ms, @start, sysdatetime());

IF @retdata = 1 SELECT wordno FROM #Str_JOIN

TRUNCATE TABLE #Str_JOIN

go

CREATE PROCEDURE XMLATTR$SCH_Str_JOIN_test @str    xml(DOCUMENT attrstr),
                                       @retdata  bit = 1,
                                       @tookms   int = NULL OUTPUT AS

DECLARE @start datetime2(3)
SELECT @start = sysdatetime()

INSERT #Str_JOIN (wordno, guid)
SELECT u.wordno, u.guid
FROM   usrdictwords u
JOIN   @str.nodes('/Root[1]/Word') AS T(Item)
  ON   u.word = T.Item.value('@Item[1]', 'nvarchar(50)')

SELECT @tookms = datediff(ms, @start, sysdatetime());

IF @retdata = 1 SELECT wordno FROM #Str_JOIN

TRUNCATE TABLE #Str_JOIN

go

CREATE PROCEDURE XMLATTR$SCH_Str_UNPACK_test @str    xml(DOCUMENT attrstr),
                                         @retdata  bit = 1,
                                         @tookms   int = NULL OUTPUT AS

DECLARE @start datetime2(3)
SELECT @start = sysdatetime()

INSERT #Str_UNPACK (word)
SELECT word = T.Item.value('@Item[1]', 'nvarchar(50)')
FROM   @str.nodes('/Root[1]/Word') AS T(Item)

SELECT @tookms = datediff(ms, @start, sysdatetime());

IF @retdata = 1 SELECT word FROM #Str_UNPACK

TRUNCATE TABLE #Str_UNPACK

go