Creating a query from an XML input parameter in a stored procedure and verifying the output

Question

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 only get back the first value for AccountType (9). I am expecting/wanting to get back all values for AccountType.

Issue 2: Once I have fixed the above issue I would like to use values from AccountType to select users from another table e.g. dbo.UserData

What I have tried:

I saw this on another SO post that you can debug but I am not sure exactly how to use this or what it's doing.

select col.query('.') as Debug

XML:

<root>
    <From>4</From>
    <AccountType>9</AccountType>
    <AccountType>5</AccountType>
    <AccountType>6</AccountType>
    <AccountType>7</AccountType>
    <AccountType>5</AccountType>
    <AccountType>4</AccountType>
    <AccountType>1</AccountType>
    <AccountType>15</AccountType>
    <AccountType>16</AccountType>
    <AccountType>1</AccountType>
    <AccountType>ivs</AccountType>
    <AccountType>10</AccountType>
    <AccountType>12</AccountType>
    <AccountType>11</AccountType>
    <AccountType>tfs</AccountType>
    <AccountType>vsa</AccountType>
    <AccountType>13</AccountType>
    <AccountType>14</AccountType>
    <GroupID>1</GroupID>
    <GroupID>5</GroupID>
</root>

Stored procedure:

CREATE PROCEDURE dbo.UserSelect
    @XMLInput XML
AS
BEGIN
    SET NOCOUNT ON;

    SELECT DISTINCT
        'AccountType' = x.v('AccountType[1]', 'nvarchar(2)')
    FROM  
        @XMLInput.nodes('/root') AS x(v)
END

Execution of stored procedure:

DECLARE @XML as XML

SET @XML = '<root>
    <From>4</From>
    <AccountType>9</AccountType>
    <AccountType>5</AccountType>
    <AccountType>6</AccountType>
    <AccountType>7</AccountType>
    <AccountType>5</AccountType>
    <AccountType>4</AccountType>
    <AccountType>1</AccountType>
    <AccountType>15</AccountType>
    <AccountType>16</AccountType>
    <AccountType>1</AccountType>
    <AccountType>ivs</AccountType>
    <AccountType>10</AccountType>
    <AccountType>12</AccountType>
    <AccountType>11</AccountType>
    <AccountType>tfs</AccountType>
    <AccountType>vsa</AccountType>
    <AccountType>13</AccountType>
    <AccountType>14</AccountType>
    <GroupID>1</GroupID>
    <GroupID>5</GroupID>
</root>'

EXEC dbo.UserSelect @XML

Dale K · Accepted Answer · 2019-05-29 23:49:11Z

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.

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

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

create function dbo.GetAccountTypeFromXML
(
  @Xml xml
)
returns table
return
  select distinct x.v.[value]('.','nvarchar(2)') AccountType
  from @XML.nodes('/root/AccountType') x(v)

Which can then be used as, for example:

select *
from dbo.UserData
where AccountType in (select AccountType from dbo.GetAccountTypeFromXML(@Xml))

answered May 29, 2019 at 23:49

Dale K

28.1k15 gold badges59 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

MAB Over a year ago

I want to thank you again for this. One additional question- when using AccountType '@XML'. How would I know if AccountType exists in '@XML'? For example there may be a situation where their are no AccountType nodes.

Dale K Over a year ago

What happened when you tried it? What do you want to happen?

MAB Over a year ago

When I tried it out it if AccountType is not present it seems like it is checking for null or empty values. If AccountType is not present in the xml then I want it to exclude checking AccountType altogether.

Dale K Over a year ago

I don't know what your final query/usage is, but if using the ITVF as suggested it will return nothing when there are not AccountType entries, so just check for nothing returned e.g. (select count(*) from dbo.GetAccountTypeFromXML(@Xml)) = 0 or greater than 0 or whatever suits :)

MAB Over a year ago

I am just wanting to checking if the node AccountType exists when executing sql select * from dbo.UserData where AccountType in (select AccountType from dbo.GetAccountTypeFromXML(@Xml)) . I think I have it figured out by checking to see if exists which returns a boolean returns 1. IF @XML.exist('/root/AccountType') = 1

|

Collectives™ on Stack Overflow

Creating a query from an XML input parameter in a stored procedure and verifying the output

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related