Как вставить элемент в столбец xml, не зная, будет ли дерево уже существовать?

Ответ 1

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

UPDATE #xmlTable
SET xmlCol.modify('
insert if (count(/root)=0) then <root><colors><color>{sql:column("color")}</color></colors></root> 
else (if (count(/root/colors)=0) then <colors><color>{sql:column("color")}</color></colors> 
else <color>{sql:column("color")}</color>) as first into 
(if (count(/root)=0) then (/) else (if (count(/root/colors)=0) then (/root) else (/root/colors)))[1]')
FROM #xmlTable
INNER JOIN #tableColors
    ON #xmlTable.id = #tableColors.id
WHERE xmlCol.exist('/root/colors/color[(text()[1])=sql:column("color")]') = 0 

Ответ 2

Лучше использовать преобразование XSL для управления XML. Вот старый блог (SQL Server 2005) об интеграции преобразований с использованием SQL CLR (имеется много другой информации):

http://blogs.msdn.com/b/mrorke/archive/2005/06/28/433471.aspx

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

update @xmlTable
set xmlCol = case 
    when cast(xmlCol as varchar(max)) = ''
        then '<root><colors><color>' + color + '</color></colors></root>'
    else {run xslt transformation, passing in @tableColors.color as XSLT parameter colorForUpdate}
    end
from @xmlTable x
inner join @tableColors y on x.id = y.id

И таблица стилей для переноса непустых экземпляров xmlCol может выглядеть примерно так:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
            version="2.0">

<xsl:output method="xml" omit-xml-declaration="yes"/>       

<xsl:param name="colorForUpdate"/>              

<xsl:template match="/root[not(colors)]">
    <xsl:copy>
        <xsl:apply-templates select="@*|node()"/>
        <colors>
            <color>
                <xsl:value-of select="$colorForUpdate"/>
            </color>
        </colors>
    </xsl:copy>
</xsl:template>

<xsl:template match="/root/colors">
    <xsl:copy>
        <xsl:apply-templates select="@*|node()"/>
        <color>
        <xsl:value-of select="$colorForUpdate"/>
    </color>
    </xsl:copy>
</xsl:template>

<xsl:template match="@*|node()">
    <xsl:copy>
        <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
</xsl:template>

</xsl:stylesheet>

Ответ 3

Используйте этот запрос:

UPDATE xmlTable
SET xmlTable.xmlCol = (
    SELECT
        CAST(REPLACE(x.myXML, '&myColor;', c.color) AS XML)
    FROM (
        SELECT *, 
            CASE
                WHEN xmlTable.xmlCol.exist('root') = 0 THEN 
                    '<root><colors><color>&myColor;</color></colors></root>'
                WHEN xmlTable.xmlCol.exist('root/colors') = 0 THEN
                    REPLACE(CONVERT(nvarchar(max), xmlTable.xmlCol), '<root>','<root><colors><color>&myColor;</color></colors>')
                ELSE
                    CONVERT(nvarchar(max), xmlTable.xmlCol)
            END AS myXML
        FROM 
            xmlTable) x
        JOIN
        tableColors c
        ON x.id = c.id
    WHERE
        xmlTable.id = x.id)

Ответ 4

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

См. мой пример в SQL Fiddle. Здесь я создал сохраненный процесс, который принимает дерево xml и столбец в качестве параметров для создания и выполнения динамического SQL-запроса. Преимущество состоит в том, что вы можете использовать один и тот же SP для вставки разных деревьев, из разных столбцов или легко изменить дерево, не опасаясь, что вы пропустили одно из операторов if. Естественно, что нормальные проблемы динамического SQL применяются и должны приниматься во внимание - например, отсутствие планов выполнения и т.д. Вы можете легко сделать это еще более общим, также принимая в качестве параметра имя таблицы/соединения в качестве параметра.

Надеюсь, это поможет кому-то в будущем, измените имена таблиц в соответствии с вашими потребностями. Здесь код (в случае, если SQL Fiddle забывает).

-- @xmlTree should be of the format '/root/colors/color', column is where to get the data from in the tableFacts table
CREATE PROCEDURE sp_insertXML(@xmlTree nvarchar(max), @column sysname)
AS
BEGIN
    DECLARE @insert nvarchar(max), @if nvarchar(max), @val nvarchar(max), @into nvarchar(max)
    DECLARE @xmlColumn nvarchar(max)='sql:column("' + @column +'")'
    DECLARE @parentTree nvarchar(max)[email protected]
    DECLARE @endTree nvarchar(max)
    DECLARE @closeTags nvarchar(max)=''
    DECLARE @thisTag nvarchar(max) 

    WHILE (LEN(@parentTree)>0)
    BEGIN
        -- Set each parameter
        SET @thisTag = RIGHT(@parentTree,CHARINDEX('/',REVERSE(@parentTree))-1)
        SET @endTree = @thisTag + COALESCE('/' + @endTree, '')
        SET @closeTags = @closeTags + '</' + @thisTag + '>'
        SET @parentTree = LEFT(@parentTree,LEN(@parentTree)-LEN(@thisTag)-1)

        -- Set the insert and into statements
        SET @if = 'if (count(' + @parentTree + '/' + @thisTag + ')=0) then '
        SET @val = '<' + REPLACE(@endTree,'/','><') + '>{' + @xmlColumn + '}' + @closeTags
        SET @insert = COALESCE(@if + @val + ' 
    else ' + @insert, @val)
        SET @into =  CASE WHEN @into IS NULL THEN '' ELSE 'if (count(' + @parentTree + '/' + @thisTag + ')=0) then ' END + ' (' + 
            CASE @parentTree WHEN '' THEN '/' ELSE @parentTree END + ')' + COALESCE(' else ' + @into,'')
    END

    DECLARE @sql nvarchar(max) = 'UPDATE xmlTable
    SET xmlCol.modify('' insert ' + @insert + ' into (' + @into + ')[1]'')
    FROM xmlTable
    INNER JOIN tableFacts
        ON xmlTable.id = tableFacts.id
    WHERE xmlCol.exist(''' + @xmlTree + '[(text()[1])=' + @xmlColumn + ']'') = 0 
      AND ISNULL(' + QUOTENAME(@column) + ','''') <> ''''' 

    EXEC sp_executesql @sql
END