SQL Server FOR XML - базовый запрос

Мне дали XML-документ, который я хочу сгенерировать с помощью сценария SQL, я не сделал ничего подобного и не смог найти примеров, которые могли бы привести меня к возможности генерировать окончательный XML-файл, который мне нужен (и я не уверен, какой из возможных методов доступен, если один лучше подходит для того, что мне нужно - EXPLICIT или PATH или, если это вообще возможно). Я надеюсь, что кто-то, имеющий некоторый опыт в создании XML из SQL, сможет указать мне правильное направление (или сказать, что то, что я пытаюсь сделать, невозможно, и что мне нужно сделать это с помощью подзапросов).

Сценарий заключается в том, что я возвращаю сведения о продукте из одной таблицы (я бы предпочел не выполнять подзапросы для каждого из необходимых мне значений).

XML-файл, который я надеюсь создать, выглядит так (у меня нет контроля над этим форматом):

<records>
    <record>
        <fields>
            <field name="id">
                <values>
                    <value>666111</value>
                </values>
            </field>
            <field name="name">
                <values>
                    <value>
                        <![CDATA[My Product Title]]>
                    </value>
                </values>
            </field>
        </fields>
    </record>
    <record>
        ...
    </record>
</records>

Первый метод, который я рассмотрел, использует FOR XML PATH

SELECT TOP 2 
    'id' AS "@name",
    p.product_id as [value], 
    p.title
FROM products p 
ORDER BY p.product_id DESC
FOR XML PATH ('field'), ROOT ('fields'), ELEMENTS;

и это дает мне XML:

<fields>
  <field name="id">
    <value>20624</value>
    <title>test154</title>
  </field>
  <field name="id">
    <value>20623</value>
    <title>test153</title>
  </field>
</fields>

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

Я также изучил FOR XML EXPLICIT

SELECT TOP 2
    1 AS Tag, NULL AS Parent,
    p.product_id AS [record!1!product_id!ELEMENT],
    NULL AS [values!2!value!ELEMENT]
FROM products p 
UNION ALL
SELECT TOP 2 
    2, 1,
    p.product_id,
    p.title
FROM products p 
ORDER BY [record!1!product_id!ELEMENT] DESC
FOR XML EXPLICIT;

Который дал мне следующий XML:

<record>
  <product_id>20624</product_id>
  <values>
    <value>test154</value>
  </values>
</record>
<record>
  <product_id>20623</product_id>
  <values>
    <value>test153</value>
  </values>
</record>

Я немного растерян в том, что могу составить запрос или получить что-то правильное (и я думаю, что я пытаюсь сделать слишком много за один поиск, и это является причиной моей проблемы). Любая помощь приветствуется - даже если она указывает мне на хорошее руководство (единственные, которые я нашел, были очень плохими, когда дело доходит до примеров - они не показывают тонкости того, как вы можете их построить / изменить)

Источник

3 ответа

Решение

Это запрос, который вы можете искать

,'' в середине есть трюк, который позволяет вам создавать несколько элементов с одинаковыми именами один под другим...

DECLARE @tbl TABLE(id INT,name VARCHAR(100));
INSERT INTO @tbl VALUES
 (666111,'My Product Title 111')
,(666222,'My Product Title 222');

SELECT 
(
    SELECT  'id' AS [field/@name]
           ,id AS [field/values/value]
           ,''
           ,'name' AS [field/@name]
           ,name AS [field/values/value]
    FOR XML PATH('fields'),TYPE
)
FROM @tbl AS tbl
FOR XML PATH('record'),ROOT('records')

Результат

<records>
  <record>
    <fields>
      <field name="id">
        <values>
          <value>666111</value>
        </values>
      </field>
      <field name="name">
        <values>
          <value>My Product Title 111</value>
        </values>
      </field>
    </fields>
  </record>
  <record>
    <fields>
      <field name="id">
        <values>
          <value>666222</value>
        </values>
      </field>
      <field name="name">
        <values>
          <value>My Product Title 222</value>
        </values>
      </field>
    </fields>
  </record>
</records>

ОБНОВЛЕНИЕ: Насколько я знаю, нет чистого способа добавить CDATA-сечений

По некоторым причинам люди в Microsoft считают, что разделы CDATA не нужны. Ну, нет, но все же иногда они требуются...

Единственный чистый способ добавить CDATA разделы должны были использовать FOR XML EXPLICIT, Другим обходным решением было поставить что-то вроде '|' + name + '#' (используйте два символа, которые никогда не появятся в ваших реальных данных.

Затем вы можете привести результат к NVARCHAR(MAX)замените эти символы на строковой базе.

Это вернет ваш XML в виде строки

SELECT 
REPLACE(REPLACE(CAST(
(
SELECT 
(
    SELECT  'id' AS [field/@name]
           ,id AS [field/values/value]
           ,''
           ,'name' AS [field/@name]
           ,'|' + name + '#' AS [field/values/value]
    FOR XML PATH('fields'),TYPE
)
FROM @tbl AS tbl
FOR XML PATH('record'),ROOT('records')
) AS NVARCHAR(MAX)),'|','<![CDATA['),'#',']]>')

На данный момент вы отбрасываете это обратно XML CDATA ушел:-(

Источник

Последний SQL-код, который я использовал:

SELECT TOP 2
(
    SELECT  
        (SELECT 'id' AS [field/@id],
        product_id [field/values/value]
        FOR XML PATH(''), TYPE),
        (SELECT 'title' AS [field/@id],
        title [field/values/value]
        FOR XML PATH(''), TYPE)
    FOR XML PATH('fields'), TYPE
)
FROM products 
FOR XML PATH('record'), ROOT('records')

Поскольку это позволяет мне немного проще управлять выводом.

Спасибо и @xdd, и особенно @Shnugo за ваши ответы! Конечное решение основано на предложении @ Shnugo, просто избегая хитрости в добавлении лишних пустых строк.

Источник

Что -то вроде того

    declare @t table (id varchar(10))

    insert into @t values ('1')
    insert into @t values ('2')

    select (
            select 
                    t.id 'fields/field/@id'
                    , t.id 'fields/field/name'
                from @t t
                for xml path(''), type
        ) 'records/record'
        for xml path('')
Источник
Другие вопросы по тегам