Creating A Query From An Xml Input Parameter In A Stored Procedure And Verifying The Output

I have created a stored procedure that reads XML data as its input. I am having two issues that I am hoping someone can help with. Issue 1: When I execute the stored procedure, I

Solution 1:

You were close, but you needed to specify the 'AccountType' node in the nodes function. And then use the value function to get the value.

selectdistinct x.v.[value]('.','nvarchar(2)') AccountType
from@XML.nodes('/root/AccountType') x(v)

In an ITVF (Inline Table Valued Function) it looks like:

createfunctiondbo.GetAccountTypeFromXML
(
  @Xml xml
)
returnstablereturnselectdistinctx.v.[value]('.','nvarchar(2)') AccountTypefrom @XML.nodes('/root/AccountType') x(v)

Which can then be used as, for example:

select *
fromdbo.UserDatawhereAccountTypein (select AccountType from dbo.GetAccountTypeFromXML(@Xml))