我已经创建了一个将XML数据作为输入读取的存储过程。我有两个问题,希望有人能帮上忙。
问题1:当我执行存储过程时,我只得到AccountType
(9)的第一个值。我期望/想要得到AccountType
的所有值。
问题2:修复了上面的问题后,我想使用AccountType
中的值从另一个表中选择用户,例如dbo.UserData
我尝试过的:
我在另一篇文章中看到了这一点,你可以调试,但我不确定如何使用它或它在做什么。
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
发布于 2019-05-30 07:49:11
已经很接近了,但您需要在nodes
函数中指定'AccountType‘节点。然后使用value
函数获取该值。
select distinct x.v.[value]('.','nvarchar(2)') AccountType
from @XML.nodes('/root/AccountType') x(v)
在ITVF (内联表值函数)中,它看起来像:
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))
https://stackoverflow.com/questions/56369698
复制相似问题