从存储过程中的 XML 输入参数创建查询并验证输出 [英] Creating a query from an XML input parameter in a stored procedure and verifying the output

问题描述

我创建了一个读取 XML 数据作为其输入的存储过程.我有两个问题，我希望有人可以提供帮助.

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.

问题 1: 当我执行存储过程时，我只返回 AccountType (9) 的第一个值.我期待/想要取回 AccountType 的所有值.

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.

问题 2: 一旦我解决了上述问题，我想使用 AccountType 中的值从另一个表中选择用户，例如dbo.UserData

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

我尝试过的:

我在另一篇可以调试的 SO 帖子上看到了这个，但我不确定如何使用它或它在做什么.

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>

存储过程:

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

存储过程的执行:

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

推荐答案

您已接近，但您需要在 nodes 函数中指定AccountType"节点.然后使用value函数获取值.

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)

在 ITVF(内联表值函数)中，它看起来像:

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)

然后可以用作，例如:

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

这篇关于从存储过程中的 XML 输入参数创建查询并验证输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！