Создайте xml с переменными именами узлов в TSQL

В моем приложении я запускаю довольно сложный запрос, используя FOR XML PATH() для создания результата xml. Я использую этот запрос в нескольких хранимых процедурах, но имена корневого узла и узла первого уровня разные. Что я хотел бы сделать, так это создать скалярную определяемую пользователем функцию, которая принимает два имени узла в качестве параметров и возвращает правильный xml. Ожидаемый результат будет выглядеть следующим образом:

<{root node name}>
    <{first level node name}  attr1="value1" attr2="value2">
        <detail attr1="value3">
            <error code="1" descr="some error"/>
            <error code="3" descr="some other error"/>
        </detail>
    </{first level node name}>
    <error code="4" descr="yet another error"/>
</{root node name}>

С {имя корневого узла} и {имя узла первого уровня} в качестве параметров

Я знаю, что могу использовать динамический SQL для этого, но это может быть очень громоздко, и я хотел бы избежать этого. Я также мог бы легко сделать это на C# или с помощью XSLT, но мне нужно сделать это полностью на SQL, поскольку он будет вызываться из обработчиков брокера службы SQL.

К сожалению, FOR XML PATH требует литерала для имени узла. Я также попытался создать xml с известными общими именами узлов, а затем попытался заменить их с помощью XQuery, но для этого также требуются литералы. И XQuery должен быть литералом, и имена узлов, используемые в конструкторах узлов, также должны быть литералами (нельзя использовать sql:variable())

Есть ли способ создать XML с динамическими именами узлов?


xml generics sql-server tsql for-xml
person P. Kouvarakis    schedule 08.03.2018    source источник
comment
Я знаю, что могу использовать динамический SQL для этого. Не в определяемой пользователем функции... Боюсь, что единственные два варианта, о которых я знаю, чтобы сделать это на уровне базы данных, - это CLR или динамический SQL (в хранимой процедуре). , а не UDF).   -  person Zohar Peled    schedule 08.03.2018
comment
Верно, но делать это в UDF — это только удобство, а не требование. Я могу превратить его в хранимую процедуру, возвращающую набор результатов из одного столбца, или использовать выходные параметры.   -  person P. Kouvarakis    schedule 08.03.2018
comment
Этот вопрос решен? Вам нужна дополнительная помощь?   -  person Shnugo    schedule 14.03.2018
comment
Не совсем. В итоге я закодировал UDF для использования общих имен элементов, а затем манипулировал xml с помощью xquery вне UDF, где имена элементов известны, поэтому xquery может быть литералом. Мне приходится повторять xquery везде, где я использую функцию, которая раздражает и подвержена ошибкам. Насколько мне известно, либо это (когда возможно), либо динамический SQL.   -  person P. Kouvarakis    schedule 14.03.2018

Ответы (1)


arrow_upward
1
arrow_downward

Поскольку имена элементов построены из имен столбцов результирующего набора, это не может работать (кроме динамического SQL или CLR), но есть спасение:

DECLARE @rowName NVARCHAR(10)=N'TheRow';
DECLARE @rootName NVARCHAR(10)=N'TheRoot';

DECLARE @OutputXml XML=
CAST(
    REPLACE(REPLACE(
    CAST((SELECT TOP 2 [name],[type] 
          FROM sys.objects 
          FOR XML PATH(N'ThisIsAStringOnlyUsedAsRowElementName')
                 ,ROOT(N'TheSameIdeaForTheRootName')
                 ,TYPE) AS NVARCHAR(MAX))
    ,N'ThisIsAStringOnlyUsedAsRowElementName',@rowName)
    ,N'TheSameIdeaForTheRootName',@rootName) AS XML);


SELECT @outputXML;

Результат

<TheRoot>
  <TheRow>
    <name>spt_fallback_db</name>
    <type>U </type>
  </TheRow>
  <TheRow>
    <name>spt_fallback_dev</name>
    <type>U </type>
  </TheRow>
</TheRoot>

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

person Shnugo    schedule 08.03.2018
comment
Двойное приведение и двойная замена... это может привести к проблемам с производительностью, если это длинный XML, но это хороший обходной путь... +1. - person Zohar Peled; 08.03.2018