使用id替换存储在xml数据中的值

I have XML in the form:

我有以下形式的XML：

<Books>
    <Book>
        <Name>Lord of the Rings</Name>
    </Book>
    <Book>
        <Name>Harry Potter</Name>
    </Book>
    <Book>
        <Name>Girl with the Dragon Tattoo</Name>
    </Book> 
</Books>

I then have a table containing two columns (Id and Name):

然后我有一个包含两列（Id和Name）的表：

1: Lord of the Rings
1：指环王
2: Harry Potter
2：哈利波特
3: Girl with the Dragon Tattoo
3：龙纹身的女孩

Using TSQL, I want to update the XML to become:

使用TSQL，我想更新XML成为：

<Books>
    <Book>
        <Name>1</Name>
    </Book>
    <Book>
        <Name>2</Name>
    </Book>
    <Book>
        <Name>3</Name>
    </Book> 
</Books>

As in, I need to do a lookup to the 'lookup' table and update the value of the nodes to be the ID instead of the name.

在中，我需要查找'lookup'表并将节点的值更新为ID而不是名称。

The only way I can think of doing this is to create a loop and to use XPath to extract the name, query the lookup table and then call modify on the XML using 'replace value of'. Is there a better way as I imagine my proposed solution will be very slow? The solution needs to be in TSQL and cannot be performed in business logic prior to calling the SP. I imagine there is a way to do this in a set based operation, but I am struggling to find it.

我能想到的唯一方法是创建一个循环并使用XPath提取名称，查询查找表，然后使用'replace value of'调用XML上的modify。有没有更好的方法，因为我认为我提出的解决方案会非常缓慢？解决方案需要在TSQL中，并且在调用SP之前无法在业务逻辑中执行。我想有一种方法可以在基于集合的操作中执行此操作，但我很难找到它。

2 个解决方案

#1

I think you're headed down the right path. And I personally don't think it will be slow. Or have you found otherwise?

我认为你走的是正确的道路。我个人认为它不会很慢。或者你有没有发现？

Consider this example from MSDN @ http://msdn.microsoft.com/en-us/library/ms190675.aspx

从MSDN @ http://msdn.microsoft.com/en-us/library/ms190675.aspx考虑这个例子

DECLARE @myDoc xml
SET @myDoc = '<Root>
<Location LocationID="10" 
            LaborHours=".1"
            MachineHours=".2" >Manu steps are described here.
<step>Manufacturing step 1 at this work center</step>
<step>Manufacturing step 2 at this work center</step>
</Location>
</Root>'
--SELECT @myDoc

I would think this right here is a very quick operation:

我认为这是一个非常快速的操作：

SET @myDoc.modify('
  replace value of (/Root/Location[1]/@LaborHours)[1]
  with (
       if (count(/Root/Location[1]/step) > 3) then
         "3.0"
       else
          "1.0"
      )
')
SELECT @myDoc

2 个解决方案

#1

更多相关文章

随机推荐