如何在不知道树是否已经存在的情况下将元素插入xml列?

I have a table of values in SQL Server 2008 from which I wish to insert the value into an XML column within a matching row of another table. The xml column may or may not have all the tags leading up to the element I want to insert.

我有一个SQL Server 2008中的值表，我希望从中将值插入到另一个表的匹配行中的XML列中。xml列可能有也可能没有指向要插入的元素的所有标记。

I can achieve this through multiple update / xml.modify statements to ensure the tags exist prior to inserting the element, but that seems really inefficient and what if I wanted to insert an element 5 or 10 tags deep?

我可以通过多个update / xml实现这一点。修改语句以确保在插入元素之前有标记存在，但这似乎非常低效，如果我想插入一个元素5或10个标记深入呢?

Here's a created example in SQL fiddle

下面是SQL fiddle中的一个创建示例

The setup is that I have 2 tables (simplified/made up here to make an understandable scenario)

设置是我有两个表(简化了/在这里创建了一个可理解的场景)

CREATE TABLE tableColors (id nvarchar(100), color  nvarchar(100))
CREATE TABLE xmlTable (id nvarchar(100), xmlCol xml)`

I need to insert the element <root><colors><color>tableColors.color</color></colors></root> into xmlTable where the id matches and the element doesn't already exist. The xmlCol can contain many more elements or even be blank. The color tag is 0 or many and the colors tag is 0 or 1.

我需要插入元素 tableColors。颜色 到xmlTable中，id匹配且元素不存在。xmlCol可以包含更多的元素，甚至是空白的。颜色标签是0或多个，颜色标签是0或1。

The final statement to insert the element in the right place makes sense, but won't work if the parent tags don't already exist.

将元素插入到正确位置的最后一条语句是有意义的，但是如果父标记还不存在，它就不能工作。

UPDATE xmlTable
SET xmlCol.modify(' insert <color>{sql:column("color")}</color> as first into (/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 

So, I need to ensure /root/colors exists before running this update statement. Please tell me I'm missing something and I don't have to explicitly do an insert of root (if empty) and then insert colors into root.

因此，在运行这个update语句之前，我需要确保/root/colors存在。请告诉我我漏掉了什么，我不需要显式地插入根(如果为空)，然后在根中插入颜色。

To further explain, here's a before and after of inserting the new element into /root/colors:

为了进一步解释，在插入新元素到/root/colors之前和之后:

New Element              XML before                                           XML after
<color>blue</color>       -blank-                                              <root><colors><color>blue</color></colors></root>
<color>green</color>      <root><vegitation>yes</vegitation></root>            <root><vegitation>yes</vegitation><colors><color>green</color></colors></root>
<color>white</color>      <root><colors><color>brown</color></colors></root>   <root><colors><color>brown</color><color>white</color></colors></root>

Again, here's a full example in SQL fiddle where I achieve what I want, but there has to be a better way. What am I missing?

同样，这里有一个SQL fiddle的完整示例，它实现了我想要的东西，但是必须有更好的方法。我缺少什么?

4 个解决方案

#1

You can include the nesting structure in your insert statement and do it with just one update like this:

您可以在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

It would be better to use an XSL transformation to manipulate the XML. Here is an old blog (SQL Server 2005) about integrating transformation using SQL CLR (there is lots of other information out there):

最好使用XSL转换来操作XML。这里有一个关于使用SQL CLR集成转换的老博客(SQL Server 2005)(还有很多其他信息):

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

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

Once you have integrated this your query could look something like:

一旦您集成了这个查询，您的查询可以如下所示:

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

And the stylesheet to handel non-empty instances of xmlCol could look somthing like:

而handel的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

Use this query :

使用这个查询:

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

This is just building upon Brian's answer which appears to be the only single XML query based solution currently possible. For me - at least for now - the single statement is enough, but if you need to insert a tree deeper into an xml structure, it would become really painful and automating this makes some degree of sense.

这是基于Brian的回答，这似乎是目前唯一可能的基于XML查询的解决方案。对我来说(至少现在是这样)，单句话就足够了，但是如果您需要将树插入到xml结构的更深处，这将变得非常痛苦，并且自动化在某种程度上是有意义的。

See my example in SQL Fiddle. Here I have created a stored proc which takes the xml tree and column as parameters to create and execute a dynamic SQL query. The benefits being that you can use the same SP to insert different trees, from different columns or easily change the tree without worry that you've missed one of the if statements. Naturally the normal issues of dynamic SQL apply and should be taken into consideration - e.g. lack of execution plans, etc. You could easily make this even more generic by also taking the table name/join clauses as a parameter.

看看我在SQL Fiddle中的例子。在这里，我创建了一个存储的proc，它以xml树和列作为参数来创建和执行动态SQL查询。好处是，您可以使用相同的SP来插入不同的树，来自不同的列，或者轻松地更改树，而不必担心漏掉了一个if语句。当然，动态SQL的常规问题是可以应用的，并且应该考虑到这些问题——例如缺少执行计划等等。

Hopefully this helps someone in the future, alter the table names to suit your needs. Here's the code (in case SQL Fiddle forgets).

希望这对将来的某人有所帮助，修改表名以适应您的需要。这里是代码(以防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)=@xmlTree
    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