Как сделать вывод FOR XML AUTO для вывода Inner Joined SELECT для обеих таблиц в их собственные соответствующие элементы?

Я пытаюсь создать хранимую процедуру, которая использует переменную BIT для вывода одного из двух возможных результатов FOR XML оператора Select с внутренним соединением. Вот текущая форма процедуры:

CREATE PROCEDURE dbo.CustomerAddress_Get
@IsAuto BIT = 0
AS
BEGIN
--@IsAuto If's
IF (@IsAuto == 0)
    BEGIN

    SELECT LEFT(GivenName,1), Surname, DOB, AccountId, Type, Balance FROM Custs
    INNER JOIN dbo.Accounts 
    ON Accounts.CustNo = Custs.CustNo
    FOR XML RAW ('CustomerAccount'), ELEMENTS, ROOT('CustomerAccountDetails')

    END
ELSE
    BEGIN

    SELECT LEFT(GivenName,1), Surname, DOB, AccountId, Type, Balance FROM Custs Customer
    INNER JOIN dbo.Accounts Account
    ON Account.CustNo = Customer.CustNo
    FOR XML AUTO, ELEMENTS, ROOT

    END
END;

И код, который я специально пытаюсь изменить для вывода учетных записей и клиентов в их собственные элементы в выводе XML AUTO, таков:

SELECT LEFT(GivenName,1), Surname, DOB, AccountId, Type, Balance FROM Custs
INNER JOIN dbo.Accounts 
ON Accounts.CustNo = Custs.CustNo
FOR XML AUTO, ELEMENTS, ROOT

Пример моего текущего вывода XML из моего Else SELECT...FOR AUTO; утверждение таким образом:

<Customer>D<Surname>Grayson</Surname><DOB>1940-04-01T00:00:00</DOB><Account><AccountId>159</AccountId><Type>Chequing</Type><Balance>0.0000</Balance></Account><Account><AccountId>160</AccountId><Type>Savings </Type><Balance>500.0000</Balance></Account></Customer>

Тем не менее, я требую, чтобы это было больше похоже на это:

<cu>
<FirstName>Catherine</FirstName>
<LastName>Abel</LastName>
<EmailAddress>[email protected]</EmailAddress>
<ca>
<AddressType>Main Office</AddressType>
<ad>
  <City>Van Nuys</City>
  <StateProvince>California</StateProvince>
</ad>

Я полагал, что именно для этого предназначен FOR XML AUTO, однако, похоже, он фактически не форматирует результирующий XML.

Теперь мой вопрос относительно этого заключается в том, как именно я могу сделать этот SELECT поместить клиента (имя, фамилия и DOB/DateOfBirth) и учетные записи (AccountId, тип и баланс) их собственные соответствующие данные в свои собственные элементы в XML АВТО выход?


xml stored-procedures tsql inner-join for-xml
person Jaeriko    schedule 16.11.2016    source источник
comment
Название вашего запроса звучит так: Я хочу получить два разных формата XML одного и того же запроса в зависимости от параметра BIT. Как мне этого добиться? Но вопрос в конце звучит так: Вывод моего FOR XML AUTO должен заключать два источника в родительские элементы. Как я могу этого добиться? На самом деле это два вопроса. Пожалуйста, уточните...   -  person Shnugo    schedule 16.11.2016
comment
@Shnugo Приношу свои извинения, если мой вопрос был запутанным, мой вопрос полностью сосредоточен на операторе ELSE Select, который будет вопросом «Как я могу получить вывод моего FOR XML AUTO для переноса двух источников в вопрос родительских элементов». Остальное, включая мою общую цель, я предоставил для контекста на случай, если потребуется изменить структуру хранимой процедуры, чтобы облегчить это. Я знаю, что мне просто нужно изменить бит на 1 или 0, чтобы получить либо If (@IsAuto = 0), либо Else, так что это не мой вопрос.   -  person Jaeriko    schedule 16.11.2016
comment
Пожалуйста, отредактируйте свой вопрос и приведите пример XML, как он выглядит на данный момент, и другой пример, как вы хотите, чтобы он выглядел...   -  person Shnugo    schedule 16.11.2016
comment
@Shnugo Я добавил текущий XML-вывод процедуры и пример того, как мне нужно, чтобы он выглядел.   -  person Jaeriko    schedule 16.11.2016
comment
AUTO означает, что SQL-Server выбирает формат за вас. Если вы хотите контролировать это, вы должны использовать FOR XML PATH и указать вывод самостоятельно. Это вариант?   -  person Shnugo    schedule 16.11.2016
comment
См. мое обновление. Если это не решит вашу проблему, убедитесь, что данные и примеры совпадают, укажите определение таблиц и предоставьте образцы данных с ожидаемыми результатами, соответствующими этим данным.   -  person Shnugo    schedule 16.11.2016
comment
@Shnugo, насколько я могу судить, к сожалению, невозможно использовать Path. Спецификации заключаются в том, что структура должна быть автоматически отформатирована, данные учетной записи должны быть добавлены в элемент под названием «Учетная запись», а сведения о клиенте добавлены в элемент под названием «Клиент», все данные столбца добавляются в отдельные элементы, и что мы используем неназванный корневой узел.   -  person Jaeriko    schedule 16.11.2016
comment
Это только для одного клиента? Если нет: Как адрес привязан к клиенту, если их много?   -  person Shnugo    schedule 16.11.2016

Ответы (1)


arrow_upward
0
arrow_downward

Это ответ на вопрос в соответствии с вашим заголовком с использованием динамически созданного оператора:

CREATE PROCEDURE dbo.CustomerAddress_Get
@IsAuto BIT = 0
AS
BEGIN
--@IsAuto If's
DECLARE @cmd NVARCHAR(MAX)=
(
    N'SELECT LEFT(GivenName,1), Surname, DOB, AccountId, Type, Balance FROM Custs
    INNER JOIN dbo.Accounts 
    ON Accounts.CustNo = Custs.CustNo
    FOR XML '
);

IF @IsAuto = 0
    SET @cmd=@cmd+N'RAW (''CustomerAccount''), ELEMENTS, ROOT(''CustomerAccountDetails'')';
ELSE
    SET @cmd=@cmd+N'AUTO, ELEMENTS, ROOT';

EXEC (@cmd);
END;

ОБНОВЛЕНИЕ: Ваш второй вопрос

Если вы используете FOR XML PATH, вы можете контролировать окончательный формат вашего XML

Не зная ваших таблиц сложно подсказать правильное решение (приведенные данные и примеры не совпадают!), но должно быть примерно так:

SELECT LEFT(cu.GivenName,1) AS [cu/FirstLetter]
      ,cu.GivenName AS [cu/FirstName]
      ,cu.Surname AS [cu/LastName]
      ,cu.DOB AS [cu/Birthdate]
      ,cu.City AS [ad/City]
      ,cu.StateProvinve AS [ad/StateProvince]
      ,ac.AccountId AS [ac/AccountId]
      ,ac.Type AS [ac/Type]
      ,Balance AS [ac/Balance] 
FROM Custs AS cu
INNER JOIN dbo.Accounts AS ac
ON ac.CustNo = cu.CustNo
FOR XML PATH('Customer'),ROOT('root')

В этом случае я бы предложил не использовать подход с динамически созданным SQL, как указано выше, а придерживаться существующего IF ... ELSE ...

ОБНОВЛЕНИЕ 2: Подвыбор вместо присоединения

Вы можете попробовать что-то вроде этого. Код создаст встроенный XML как вложенный выбор:

SELECT Custs.*
     ,(
        SELECT Accounts.* 
        FROM dbo.Accounts 
        WHERE Accounts.CustNo = Custs.CustNo
        FOR XML AUTO, ELEMENTS,TYPE
      ) AS [ad]
FROM Custs AS [cu]
FOR XML AUTO, ELEMENTS, ROOT
person Shnugo    schedule 16.11.2016
comment
Я знаю, что выходные данные XML и данный пример не совпадают, мне просто нужно, чтобы они были похожи по структуре на пример. Я не совсем уверен, как должен выглядеть вывод, только то, что учетная запись и клиент должны быть отдельными, и что пример - это то, как XML AUTO должен форматироваться. - person Jaeriko; 16.11.2016
comment
Спасибо за вашу помощь @Shnugo, я полагаю, что, возможно, неправильно истолковал спецификации как более сложные, чем они должны быть, и понял, что уже выполнил их. - person Jaeriko; 16.11.2016
comment
@Jaeriko Отлично, всегда хорошо, если узнаешь, что жизнь проста, как ты думал :-) Удачного кодирования! - person Shnugo; 16.11.2016