Создание запроса из входного параметра XML в хранимой процедуре и проверка вывода
Я создал хранимую процедуру, которая считывает XML-данные в качестве входных данных. У меня есть две проблемы, с которыми я надеюсь, что кто-то может помочь.
Выпуск 1: Когда я выполняю хранимую процедуру, я получаю только первое значение для AccountType (9). Я ожидаю/хочу вернуть все значения для AccountType.
Выпуск 2: После того, как я устранил указанную выше проблему, я хотел бы использовать значения из AccountType для выбора пользователей из другой таблицы, например. dbo.UserData
Что я пробовал:
Я видел это в другом сообщении SO, которое вы можете отлаживать, но я точно не знаю, как это использовать или что оно делает.
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
Ответы 1
Вы были близки, но вам нужно было указать узел «AccountType» в функции nodes. А затем используйте функцию 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))
Что произошло, когда вы попробовали это? Что вы хотите, чтобы произошло?
Когда я попробовал это, если AccountType отсутствует, кажется, что он проверяет нулевые или пустые значения. Если AccountType отсутствует в xml, я хочу, чтобы он вообще исключал проверку AccountType.
Я не знаю, каков ваш окончательный запрос/использование, но если использовать ITVF, как было предложено, он ничего не вернет, если нет записей AccountType, поэтому просто проверьте, не возвращено ли ничего, например. (select count(*) from dbo.GetAccountTypeFromXML(@Xml)) = 0 или больше 0 или что-то другое :)
Я просто хочу проверить, существует ли узел AccountType при выполнении sql select * from dbo.UserData where AccountType in (select AccountType from dbo.GetAccountTypeFromXML(@Xml)) . Я думаю, что понял это, проверив, существует ли функция, которая возвращает логическое значение, возвращающее 1. IF @XML.exist('/root/AccountType') = 1
Если вам не удастся это решить, просто задайте другой вопрос.
Я хочу еще раз поблагодарить вас за это. Еще один вопрос - при использовании AccountType '@XML'. Как узнать, существует ли AccountType в '@XML'? Например, может возникнуть ситуация, когда у них нет узлов AccountType.