Helpers - компьютеры, интернет, программирование

Проблема с получением данных SQL в виде XML

У меня возникла проблема с SQL-запросом, предназначенным для возврата результирующих данных в виде XML. Вот дамп кода, чтобы показать вам все, что происходит:

SQL-запрос (Примечание: имена таблиц и столбцов отредактированы)

with resultdata as
(
    SELECT 
    (
        select * from Table1 (nolock)
        where column1 = 99999 and column2 = -1 for xml auto, type
    ) as tabledata 
    UNION ALL
    SELECT
    (   
        select * from Table2 (nolock)
        where column1 = 99999 and column2 = -1 for xml auto, type 
    )
    UNION ALL
    SELECT
    (
        select * from Table3 (nolock)
        where column1 = 99999 and column2 = -1 for xml auto, type
    )
    UNION ALL
    SELECT 
    (
        select * from Table4 (nolock)
        where column1 = 99999 and column2 = -1 for xml auto, type
    )
    UNION ALL
    SELECT 
    (
        select * from Table5 (nolock)
        where column1 = 99999 and column2 = -1 for xml auto, type
    )
    UNION ALL
    SELECT 
    (
        select * from Table6 (nolock)
        where column1 = 99999 and column2 = -1 for xml auto, type
    )
    UNION ALL
    SELECT 
    (
        select * from Table7 (nolock)
        where column1 = 99999 and column2 = -1 for xml auto, type
    )
    UNION ALL
    SELECT 
    (
        select * from Table8 (nolock)
        where column1 = 99999 and column2 = -1 for xml auto, type
    )
)

select * from resultdata result for xml auto, elements

Это возвращает результат XML, который выглядит следующим образом (большая часть XML отредактирована, комментарии находятся там, где фактические данные):

<result>
  <tabledata>
    <!--Table1 results-->
  </tabledata>
</result>
<result>
  <tabledata>
    <!--Table2 results-->
  </tabledata>
</result>
<result>
  <tabledata>
    <!--Table3 results-->
  </tabledata>
</result>
<result>
  <tabledata>
    <!--Table4 results-->
  </tabledata>
</result>
<result>
  <tabledata>
    <!--Table5 results-->
  </tabledata>
</result>
<result>
  <tabledata>
    <!--Table6 results-->
  </tabledata>
</result>
<result>
  <tabledata>
   <!--Table7 results-->
  </tabledata>
</result>
<result>
  <tabledata>
    <!--Table8 results-->
  </tabledata>
</result>

Очевидно, что это искаженный XML, но я не могу его переработать, чтобы он давал мне результаты в правильном формате, примерно так:

<result>
  <tabledata>
    <!--Table1 results-->
  </tabledata>
  <tabledata>
    <!--Table2 results-->
  </tabledata>
  <tabledata>
    <!--Table3 results-->
  </tabledata>
  <tabledata>
    <!--Table4 results-->
  </tabledata>
  <tabledata>
    <!--Table5 results-->
  </tabledata>
  <tabledata>
    <!--Table6 results-->
  </tabledata>
  <tabledata>
   <!--Table7 results-->
  </tabledata>
  <tabledata>
    <!--Table8 results-->
  </tabledata>
</result>

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

Кроме того, как еще одно замечание, XML, который я хочу, должен выглядеть именно так, потому что в конечном итоге он потребляется XSLT, над которым у меня нет контроля. Старая версия этого запроса возвращала каждый элемент tabledata как другое имя столбца, а затем выполняла замену строки в коде C#, который его вызывает. Были проблемы с производительностью при выполнении нескольких замен строк в этом XML (может быть более 100 тысяч строк), поэтому ответ заключался в том, чтобы изменить запрос, чтобы он возвращал данные в том виде, в котором они нам нужны.

xml sql sql-server
03.09.2019

  • Удаление типа for xml auto из всех подзапросов вызвало ошибку: в списке выбора можно указать только одно выражение, если подзапрос не введен с помощью EXISTS. Я также попытался удалить автоматическую часть и просто оставить для xml, но это дает синтаксическую ошибку. 03.09.2019
  • Что потребляет этот XML? Может ли результат, который вы получаете, заменить все экземпляры ‹/result›‹result› ничем? 04.09.2019
  • Это XSLT, который использует его, но в конечном итоге я пытаюсь избежать замены строки. Текущая реализация делает это, и мой босс, по сути, хочет, чтобы это было удалено из соображений производительности. 04.09.2019

Ответы:


1

Просто еще один вариант. Это вернет желаемые результаты.

Select (select * from Table1 (nolock) where column1 = 99999 and column2 = -1 for XML auto, type, root('tabledata') )
      ,(Select * from Table2 (nolock) where column1 = 99999 and column2 = -1 for XML auto, type, root('tabledata') )
      ,(Select * from Table3 (nolock) where column1 = 99999 and column2 = -1 for XML auto, type, root('tabledata') )
 For XML Path(''),Root('results')
03.09.2019
  • @KyleRone Рад помочь 04.09.2019

    • 2

    В SQL Server переменная XML, столбец или результат запроса FOR XML представляет собой «XML-фрагмент» без единого элемента верхнего уровня.

    Для XML-запросов, если вам нужен XML-документ (с корневым элементом верхнего уровня), вы добавляете ROOT для FOR XML.

    03.09.2019
  • Можете ли вы объяснить немного больше, как я могу изменить свой SQL, чтобы использовать это? Я попытался заменить финал для xml auto, элементы в последней строке на for xml auto, root, и все, что я сделал, это добавил корневой элемент вокруг неправильно сформированного xml в моем вопросе. Мне нужно, чтобы корневой элемент назывался результатом, как в моем вопросе. 04.09.2019
  • Помимо отсутствия одного корневого элемента, как неправильно сформирован XML? 04.09.2019
  • Вот что я имею в виду, нет ни одного корневого элемента. Но ответ, к сожалению, не так прост, как просто добавить корневой элемент, потому что мне нужно, чтобы узел результата был корневым элементом, и я обновил свой вопрос, чтобы объяснить, почему. 04.09.2019
  • Я не уверен, что вам нужно. Можете ли вы отредактировать вопрос, включив в него образец таблицы DDL, исходные данные и желаемый вывод XML? 04.09.2019

    • 3

    Если я правильно понимаю ваш вопрос, вы должны объединить свой запрос для каждой таблицы вместе, а затем применить форматирование XML. Нравится...

    WITH resultdata AS
(
    SELECT (
               SELECT *
               FROM   (
                          SELECT *
                          FROM   Table1 (NOLOCK)
                          WHERE  column1 = 99999
                                 AND column2 = -1
                          UNION ALL
                          SELECT *
                          FROM   Table2 (NOLOCK)
                          WHERE  column1 = 99999
                                 AND column2 = -1
                          UNION ALL
                          SELECT *
                          FROM   Table3 (NOLOCK)
                          WHERE  column1 = 99999
                                 AND column2 = -1
                          UNION ALL
                          SELECT *
                          FROM   Table4 (NOLOCK)
                          WHERE  column1 = 99999
                                 AND column2 = -1
                          UNION ALL
                          SELECT *
                          FROM   Table5 (NOLOCK)
                          WHERE  column1 = 99999
                                 AND column2 = -1
                          UNION ALL
                          SELECT *
                          FROM   Table6 (NOLOCK)
                          WHERE  column1 = 99999
                                 AND column2 = -1
                          UNION ALL
                          SELECT *
                          FROM   Table7 (NOLOCK)
                          WHERE  column1 = 99999
                                 AND column2 = -1
                          UNION ALL
                          SELECT *
                          FROM   Table8 (NOLOCK)
                          WHERE  column1 = 99999
                                 AND column2 = -1
                      ) AS x
               FOR XML AUTO, TYPE
           ) AS tabledata
)
SELECT * FROM resultdata AS result FOR XML AUTO, ELEMENTS;
    03.09.2019
  • Поэтому, когда я отредактировал свой фактический запрос, чтобы он выглядел так, я получил эту ошибку: все запросы, объединенные с использованием оператора UNION, INTERSECT или EXCEPT, должны иметь одинаковое количество выражений в своих целевых списках. В реальном запросе некоторые из подзапросов используют *, в то время как другие явно указывают, какие столбцы им нужны. Может ли это быть проблемой? Если это так, то я не уверен, что это сработает, каждая таблица полууникальна, но наверняка не все они имеют одинаковое количество столбцов. 04.09.2019
  • Если они не имеют одинакового количества столбцов, вы не можете использовать UNION. 04.09.2019
  • Ааа ну тогда. Думаю, мне придется вернуться к чертежной доске. Спасибо за знания. 04.09.2019

    • 4

    Как насчет следующего решения, которое позволяет создавать окончательный XML с его корнем из таблиц БД с различной структурой.

    SQL

    -- DDL and sample data population, start
DECLARE @tbl1 TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, city VARCHAR(30));
INSERT INTO @tbl1
VALUES
('Miami')
, ('Orlando');

DECLARE @tbl2 TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, [state] VARCHAR(30));
INSERT INTO @tbl2
VALUES
('Florida')
, ('Texas');
-- DDL and sample data population, end

WITH resultdata (tabledata) AS
(
    SELECT 
    (
        SELECT * FROM @tbl1
        FOR XML PATH('row'), TYPE, ROOT('tbl1')
    ) AS [tbl1]
    UNION ALL
    SELECT
    (   
        SELECT * FROM @tbl2
        FOR XML PATH('row'), TYPE, ROOT('tbl2')
    )
)
SELECT * 
FROM resultdata
FOR XML PATH(''), TYPE, ROOT('result');

    Выходной XML

    <result>
  <tabledata>
    <tbl1>
      <row>
        <ID>1</ID>
        <city>Miami</city>
      </row>
      <row>
        <ID>2</ID>
        <city>Orlando</city>
      </row>
    </tbl1>
  </tabledata>
  <tabledata>
    <tbl2>
      <row>
        <ID>1</ID>
        <state>Florida</state>
      </row>
      <row>
        <ID>2</ID>
        <state>Texas</state>
      </row>
    </tbl2>
  </tabledata>
</result>
    03.09.2019
    Новые материалы
    Интуитивное понимание тензоров в машинном обучении
    Тензор является важной концепцией во многих научных областях, таких как математика, физика, обработка сигналов и компьютерное зрение, и это лишь некоторые из них. В математике тензор — это..
    Использование машинного обучения для диагностики болезни Альцгеймера, часть 4
    Маркеры семантической согласованности для ранней диагностики болезни Альцгеймера (arXiv) Автор: Давиде Колла , Маттео Дельсанто , Марко Агосто , Бенедетто Витиелло , Даниэле Паоло Радичони..
    Почему объяснимость так важна прямо сейчас?
    По мере того, как системы искусственного интеллекта и инструменты на основе машинного обучения распространяются в нашей повседневной жизни, как практики, так и критики все чаще заявляют о..
    Анимированный математический анализ
    Использование Manim для создания математических анимированных визуализаций Визуализация данных помогает понять скрытые закономерности в данных, которые невозможно визуализировать..
    Создание простого слайдера изображений с помощью JavaScript
    Узнайте, как создать базовый слайдер изображений с помощью HTML, CSS и JavaScript. Введение В этом уроке мы создадим удобный слайдер изображений, используя JavaScript, HTML и CSS. Ползунок..
    Создание базы данных с помощью супергероя «Python»
    В этом посте мы узнаем, как создать «базу данных SQLite с помощью модуля python sqlite3, создав простую функцию входа и регистрации. Готовы ли вы к этому путешествию? Если да , давайте приступим..
    ИИ для чайников: руководство для начинающих по пониманию будущего технологий
    Вы чувствуете, что остались позади в мире ИИ? Не волнуйтесь, вы не одиноки! Со всей этой шумихой вокруг искусственного интеллекта может быть трудно понять, с чего начать. Но не позволяйте сленгу..