Расположение динамической вставки узла XML в существующий экземпляр с использованием XML-DML

Мне не удалось найти никакой документации, можно ли динамически вставить узел xml в существующий экземпляр xml в SQL Server (2012). Я знаю, что вы можете вставить или заменить условное значение, но, похоже, нет никакой документации, если место вставки может быть выполнено динамически на основе некоторых условий. Например, скажем, у меня есть этот вызов XML-DML:

SET @xml.modify('insert <SecondaryContact><Name>{ sql:variable("@contactName") }</Name>
                    <Phone>{ sql:variable("@contactPhone") }</Phone>
                    <Email>{ sql:variable("@contactEmail") }</Email></SecondaryContact>
                    after (/Project/PrimaryContact)[1]');

Будет ли модификация, в которой узел, указанный после ключевого слова after, является условным, допустимым синтаксисом? Ниже приводится пример того, что я имею в виду:

SET @xml.modify('insert <TechnicalContact><Name>{ sql:variable("@contactName") }</Name>
                    <Phone>{ sql:variable("@contactPhone") }</Phone>
                    <Email>{ sql:variable("@contactEmail") }</Email></TechnicalContact>
                    after (
                        if(count(/Project/SecondaryContact) = 0)
                        then (/Project/PrimaryContact)[1]
                        else (/Project/SecondaryContact)(1)
                    )');

Это единственный способ динамически выбрать место для IF..ELSE операторов вне операторов XML DML, или мой пример XML-DML действителен?

ИЗМЕНИТЬ Пример XML:

<root>
    ...
    <PrimaryContact Id="1234">
        <Name>John Doe</Name>
        <Phone>555-555-5555</Phone>
        <Email>[email protected]</Email>
    </PrimaryContact>
    <SecondaryContact Id="1236">   <--OPTIONAL
        <Name>John Doe1</Name>
        <Phone>555-555-5556</Phone>
        <Email>[email protected]</Email>
    </SecondaryContact>
    <TechnicalContact Id="2234"> <--OPTIONAL
        <Name>John Doe2</Name>
        <Phone>555-555-5255</Phone>
        <Email>[email protected]</Email>
    </TechnicalContact>
    ...
</root>

Я понимаю, что структура не идеальна. Это должно быть <Contacts><Contact Type="PRIMARY" Id="1234">...</Contact>...</Contacts>, но хотелось посмотреть, возможно ли место динамической вставки в операторе DML. Использование курсора подходит для этого вопроса, как и для одноразового обновления.


xml sql-server sql-server-2012 sqlxml xml-dml
person JNYRanger    schedule 04.11.2016    source источник
comment
Пожалуйста, предоставьте пример вашего XML для обоих условий и ожидаемого результата для обоих случаев.   -  person Shnugo    schedule 04.11.2016
comment
@Shnugo Добавил пример структуры. Это скорее теоретический вопрос о том, возможно ли это в рамках одного оператора DML.   -  person JNYRanger    schedule 04.11.2016
comment
.modify() очень ограничен ... Это может быть довольно просто с FLWOR-query(), что-то вроде _3 _... Но это зависит от ваших реальных потребностей ... Вы проверили мой ответ?   -  person Shnugo    schedule 04.11.2016
comment
@Shnugo да, я посмотрел. Я меняю схему XML, поэтому на самом деле в этом нет необходимости, но поскольку я не смог найти никакой документации / ресурсов, которые показали, возможен ли этот синтаксис, я решил, что спрошу. Ваш подход интересен и исключает курсоры, но не может выполнять вставку между узлами <PrimaryContact> и <TechnicalContact>, если вторичный не существует и теперь необходим. (Я знаю, что не дал этого ясно в вопросе!)   -  person JNYRanger    schedule 04.11.2016
comment
Другой подход: используйте CTE для измельчения вашего XML, используйте .query() для получения всех незатронутых узлов, .value() для извлечения затронутых значений. Затем используйте простой оператор SELECT ... FOR XML PATH(), чтобы перестроить свой XML так, как вам нужно ...   -  person Shnugo    schedule 04.11.2016

Ответы (3)


arrow_upward
4
arrow_downward

Это предложение не совсем то же самое, поскольку оно будет вставлено после того, что будет позже в документе, а не после SecondaryContact, но я подозреваю, что в вашем случае это то же самое:

SET @xml.modify('insert 
    <TechnicalContact><Name>{ sql:variable("@contactName") }</Name>
    <Phone>{ sql:variable("@contactPhone") }</Phone>
    <Email>{ sql:variable("@contactEmail") }</Email></TechnicalContact>

    after (/Project/*[
        local-name(.) = "SecondaryContact" 
        or local-name(.) = "PrimaryContact" 
    ])[last()]
');

Or:

if @xml.value('count(/Project/SecondaryContact)', 'int') = 0
begin
  SET @xml.modify('insert <TechnicalContact><Name>{ sql:variable("@contactName") }</Name>
                <Phone>{ sql:variable("@contactPhone") }</Phone>
                <Email>{ sql:variable("@contactEmail") }</Email></TechnicalContact>
                after (/Project/PrimaryContact)[1]
                ');
end else begin
  SET @xml.modify('insert <TechnicalContact><Name>{ sql:variable("@contactName") }</Name>
                <Phone>{ sql:variable("@contactPhone") }</Phone>
                <Email>{ sql:variable("@contactEmail") }</Email></TechnicalContact>
                after (/Project/SecondaryContact)[1]
                ');
end
person Ben    schedule 04.11.2016
comment
Это то, что у меня есть в настоящее время, но я хотел знать, можно ли его сжать в один оператор DML. - person JNYRanger; 04.11.2016
comment
Мне это нравится (+1 с моей стороны), и я признаю, что это была первая идея, которая у меня возникла ... Но я предположил, что проблема реального мира находится в таблице и процедурном multi-statement-sql < / i> заставит использовать курсор или какой-то другой цикл ... Может быть, это просто и просто идеально :-) - person Shnugo; 04.11.2016
comment
Ну, вам не нужно делать цикл, вы можете вставить тест в предложение WHERE и выполнить два обновления? - person Ben; 04.11.2016
comment
Циклы не обязательно так уж плохи :-) По крайней мере, их легко понять! Я не фанат курсоров, я предпочитаю использовать переменную declare @temp table. - person Ben; 04.11.2016

arrow_upward
1
arrow_downward

Пока вы не предоставите еще несколько примеров (см. Мой комментарий), лучшее, что я могу придумать, - это создать узел для внешней вставки и вставить его как последний

Но - совершенно уверен - есть подход получше ...

DECLARE @contactName NVARCHAR(100)='TestName';
DECLARE @contactPhone NVARCHAR(100)='TestPhone';
DECLARE @contactEmail NVARCHAR(100)='TestEmail';

DECLARE @tbl TABLE(ID INT IDENTITY,Descr VARCHAR(100),XmlColumn XML);
INSERT INTO @tbl VALUES
 ('With secondary'
 ,N'<Project>
<PrimaryContact>test Primary</PrimaryContact>
<SecondaryContact>test Secondary</SecondaryContact>
</Project>')
,('Only primary'
,N'<Project>
<PrimaryContact id="prim">test Primary</PrimaryContact>
</Project>');

UPDATE @tbl SET XmlColumn.modify
(
    N'insert sql:column("x.NodeToInsert") as last into (/Project)[1]'
)
FROM @tbl
CROSS APPLY
(
    SELECT
    (
        SELECT(
                    SELECT @contactName AS [Name]
                          ,@contactPhone AS [Phone]
                          ,@contactEmail AS [Email]
                    WHERE XmlColumn.exist('/Project/SecondaryContact')=0
                    FOR XML PATH('SecondaryContact'),TYPE
               ) AS [node()]
              ,(
                    SELECT @contactName AS [Name]
                          ,@contactPhone AS [Phone]
                          ,@contactEmail AS [Email]
                    WHERE XmlColumn.exist('/Project/SecondaryContact')=1
                    FOR XML PATH('TechnicalContact'),TYPE
               ) AS [node()]
        FOR XML PATH(''),TYPE
    ) AS NodeToInsert
) AS x

SELECT * FROM @tbl

ОБНОВИТЬ

Другой подход: используйте CTE для измельчения вашего XML, используйте .query() для получения всех незатронутых узлов, .value() для извлечения затронутых значений. Затем используйте простой оператор SELECT ... FOR XML PATH (), чтобы перестроить ваш XML так, как вам нужно ...

person Shnugo    schedule 04.11.2016

arrow_upward
1
arrow_downward

Вы можете построить последовательность из SecondaryContact и PrimaryContact в этом порядке и добавить узел после первое появление.

insert 
  <TechnicalContact>
    <Name>{ sql:variable("@contactName") }</Name>
    <Phone>{ sql:variable("@contactPhone") }</Phone>
    <Email>{ sql:variable("@contactEmail") }</Email>
  </TechnicalContact>
after (
      /Project/SecondaryContact,
      /Project/PrimaryContact
      )[1]
person Mikael Eriksson    schedule 04.11.2016
comment
В зависимости от порядка, в котором вы вводите элементы в списке после ключевого слова after, он выберет первый, который существует для синглтона? Я знаю, что SQL DML не реализует весь XQuery, поэтому я не был уверен, поддерживается ли это. Если так, то это ТОЧНО то, что я искал ... - person JNYRanger; 04.11.2016
comment
@JNYRanger Конечно, он будет вставлен после первого найденного элемента. Теперь, если у вас есть несколько элементов SecondaryContact, они все равно будут вставляться после первого найденного SecondaryContact, а не последнего. Если вы хотите, чтобы TechnicalContact был вставлен после последнего вторичного или после последнего первичного, вы должны изменить порядок элементов в конструкции и использовать [last ()] в качестве последнего предиката. - person Mikael Eriksson; 04.11.2016
comment
Построенная вами последовательность создает новый xml, который используется для поиска. Сначала за вторичными элементами следуют первичные элементы, а затем он ищет первый элемент в этой последовательности и вставляет технический элемент после этого элемента в исходный XML. - person Mikael Eriksson; 04.11.2016
comment
Это именно то, что я пытался определить, как это сделать. Потрясающие! - person JNYRanger; 04.11.2016