基于XML节点值获取记录的SQL选择查询？

Question

我有一个ntext数据类型的列，不是XML。它存储所有的XML数据。我需要根据XML节点值获取记录。 =>输入值为CpsiaId = 456，应该返回所有具有此值的记录在XML中。

我尝试了select * from tableName where convert(xml,column_name).value('data((/root/ProductInformation/CPSIA/CpsiaDetails/Item/CpsiaId)[1])','int') = 456，但它没有起作用......有什么想法或其他方法可以根据XML节点值获取记录。 示例Xml：

<root>
  <ProductInformation>
    <Name> Truck with Battery Charger</Name>
    <Description>Fr.</Description>
    <CPSIA>
      <CpsiaDetails>
        <Item>
          <CpsiaId>456</CpsiaId>
          <CpsiaMessage>waring</CpsiaMessage>
        </Item>
        <Item>
          <CpsiaId>236</CpsiaId>
          <CpsiaMessage>to health</CpsiaMessage>
        </Item>
      </CpsiaDetails>
    </CPSIA>
  </ProductInformation>
</root>

- DotNetDeveloper

Answer 1

convert(xml,column_name).exist('/root/ProductInformation/CPSIA/CpsiaDetails/Item[CpsiaId=456]') = 1

这个方法是可行的，如果你需要动态值，可以用sql:variable()替换常量(456)，例如（假设一个数字变量@i）：

'/root/ProductInformation/CPSIA/CpsiaDetails/Item[CpsiaId=sql:variable("@i")]'

编辑：

如您所需，以下是示例代码：

DECLARE @t nvarchar(MAX);
SET @t = '<root>
  <ProductInformation>
    <Name> Truck with Battery Charger</Name>
    <Description>Fr.</Description>
    <CPSIA>
      <CpsiaDetails>
        <Item>
          <CpsiaId>456</CpsiaId>
          <CpsiaMessage>waring</CpsiaMessage>
        </Item>
        <Item>
          <CpsiaId>236</CpsiaId>
          <CpsiaMessage>to health</CpsiaMessage>
        </Item>
      </CpsiaDetails>
    </CPSIA>
  </ProductInformation>
</root>'

DECLARE @i int;
SET @i = 456;

SELECT convert(xml,@t).exist('/root/ProductInformation/CPSIA/CpsiaDetails/Item[CpsiaId=sql:variable("@i")]')

- Lucero

---

@Lucero，你能贴出动态示例查询吗？我尝试了一下，但是它抛出了一个错误：“xml数据类型方法'exist'的参数1必须是字符串字面值。” - DotNetDeveloper

@user594014 我已更新我的答案，使用了两种语法，你只需要测试一下就可以了。 - Conrad Frix

@user594014，我猜我的更快，CTE版本使用CROSS APPLY创建了许多虚拟行，并且需要对XML数据进行更多的XQuery运行和转换（value方法）。但有时优化器的工作方式很神秘...如果您需要经常运行此查询以获得更好的性能，则可能需要查看XML索引。 - Lucero

@Conrad Frix，文档说不行，但是即使如此，为了之后运行查询可能仍然可以创建一个带有索引的XML列的“临时”表，这取决于百万行中XML文档的复杂性以及查询此值的频率。另请参阅http://msdn.microsoft.com/en-us/library/ms191497.aspx。 - Lucero

@user594014，对于XML列（就像可以在XML列上创建索引一样），可以使用xml数据类型的modify()方法来实现。但是，只要您的列是ntext，这将不太容易实现。 - Lucero

Answer 2

使用公共表达式（CTE）将其转换为XML，然后进行CROSS Apply可能更容易。以下是一个示例：

Declare @test table (id int identity, xmlData nvarchar(max))
Insert Into @test 
(xmldata)
Values 
('<root>
  <ProductInformation>
    <Name> Truck with Battery Charger</Name>
    <Description>Fr.</Description>
    <CPSIA>
      <CpsiaDetails>
        <Item>
          <CpsiaId>456</CpsiaId>
          <CpsiaMessage>waring</CpsiaMessage>
        </Item>
        <Item>
          <CpsiaId>236</CpsiaId>
          <CpsiaMessage>to health</CpsiaMessage>
        </Item>
      </CpsiaDetails>
    </CPSIA>
  </ProductInformation>
</root>'),
('<root>
  <ProductInformation>
    <Name> Truck with Battery Charger</Name>
    <Description>Fr.</Description>
    <CPSIA>
      <CpsiaDetails>
        <Item>
          <CpsiaId>999</CpsiaId>
          <CpsiaMessage>waring</CpsiaMessage>
        </Item>
        <Item>
          <CpsiaId>123</CpsiaId>
          <CpsiaMessage>to health</CpsiaMessage>
        </Item>
      </CpsiaDetails>
    </CPSIA>
  </ProductInformation>
</root>')

;with cte as (select id, convert(xml,t.xmlData) xdata from @test t )
SELECT t.*
     from cte inner join @test t
     on cte.id = t.id
    cross apply cte.xdata.nodes('//root/ProductInformation/CPSIA/CpsiaDetails/Item') x(nd)
WHERE
    x.nd.value('CpsiaId[1]','int') =456

使用Lucero的答案来对表进行选择（而不是xml变量）

DECLARE @i int;
SET @i = 456;
SELECT * 
FROM 
    @test 
WHERE
    convert(xml,xmlData).exist('/root/ProductInformation/CPSIA/CpsiaDetails/Item[CpsiaId=sql:variable("@i")]') = 1

- Conrad Frix

---

@Conrad Frix...这个结果符合预期，谢谢你。你的示例和Lucero在另一篇帖子中提到的哪个更快？我的数据库有100万条记录。 - DotNetDeveloper

@user594014。我猜Lucero的可能会更好，但这只是猜测。我建议你两个都试一下，看看哪个更适合。 - Conrad Frix

@ConradFrix 是的，你猜对了，Lucero的方法更快，我感谢你们俩帮助我。 - DotNetDeveloper

@ConradFrix 非常感谢，我找到了答案，但有点隐藏。另外，您怎么在编辑器中从 Visual Studio 或 SQL Server 复制时保留颜色语法呢？我以前一直没有成功过… 能告诉我吗？ - DotNetDeveloper

@user594014 你可以选择代码块并按下{}按钮。你也可以使用Markdown。如果你想要尝试一些实验，可以前往Markdown 沙盒。 - Conrad Frix

@ConradFrix...如果我想基于ID更新CpsiaMessage节点的列值，例如输入ID：456和InputMessage：“吸烟有害健康”，通过xpath是否可能实现？ - DotNetDeveloper