从 MS-SQL Server 2008 中的 XML 字段中提取值 [英] Extracting values from XML field in MS-SQL Server 2008

问题描述

我试图从存储在单个 XML 字段中的 MS-SQL Server 2008 数据库中提取四条信息.这是我第一次不得不使用 XML，所以我遇到了一些麻烦，这就是为什么我只有我试图提取的数据.我曾尝试使用其他帖子来解决我的问题，但显然没有运气.

I am trying to extract four pieces of information from a MS-SQL Server 2008 database which is stored in a single XML field. This is the first time I have ever had to work with XML so I am having a bit of trouble and which is why I only have the data I am trying to extract. I have tried using other postings to solve my problem, but obviously with no luck.

这四个信息首先是项目经理"，然后是价值"，然后是利润中心"，然后是价值.利润中心"中的值将用于在两个表之间进行连接.下面是存储在此字段中的 XML 数据示例.

The four pieces of information is first the "Project Manager" and then the "Value" and then 2nd is the "Profit Center" and then that value. The value from the "Profit Center" is going to be used to do a join between two tables. Below is a sample of the XML data that is stored in this field.

    <ArrayOfEntityPropertyOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
     xmlns:xsd="http://www.w3.org/2001/XMLSchema">
     <EntityPropertyOfString>
       <Name>Project Manager</Name>
       <Value>DBD</Value>
     </EntityPropertyOfString>
     <EntityPropertyOfString>
       <Name>Profit Center</Name>
       <Value>211</Value>
     </EntityPropertyOfString>
    </ArrayOfEntityPropertyOfString>

因此，在此示例中，我需要使用利润中心"值211"将两个表全部连接到 MS-SQL 查询中.该信息可以称为tblProfitCenter"的表和持有它的字段prftData".

So in this example I need to use the "Profit Center" value "211" to join the two tables all in a MS-SQL query. The table that this information can be called "tblProfitCenter" and the field holding it "prftData".

如果prftData"中的数据不是 XML 格式，而是一个包含利润中心 ID 并执行连接的常规整数字段，则这是一个组合查询，该查询将执行相同的工作.

Here is a made up query that would do the same job if the data in "prftData" was not in XML, but instead was a regular integer field holding the profit center id and performing the join.

    SELECT md.LName, md.FName, pc.ProfitCenterName
    FROM tblMainDataCenter md
    LEFT OUTER JOIN tblProfitCenter pc ON md.pcID = pc.prftData

这是针对我工作的项目，需要能够超越此范围.通常我会学习 XML 来解决这个问题，但时间不允许这样做.在我有机会学习 XML 之前，我将不胜感激.

This is for a project at where I work and need to be able move beyond this. Normally I would be learning XML to solve this, but time will not allow this. Until I do get a chance to learn XML, I would appreciate any help.

推荐答案

您可以在 XML 列上使用 value 函数从 XML 中提取数据，如下所示:

You can use the valuefunction on an XML column to extract data from the XML like this:

SELECT col.value('(/ArrayOfEntityPropertyOfString/EntityPropertyOfString[Name="Profit Center"]/Value)[1]', 'int')
FROM tbl

假设您的表名为 tbl，而 XML 列名为 col.

assuming your table is named tbland the XML column is named col.

第一个参数是 XPath 1 表达式，第二个参数是目标数据类型.请注意，这些字符串在 SQL Server 中必须是固定字符串，不能动态构建.G.通过字符串连接.

The first argument is an XPath 1 expression, and the second the target data type. Please note that these strings must be fix strings in SQL Server and cannot be built dynamically e. g. by string concatenation.

这篇关于从 MS-SQL Server 2008 中的 XML 字段中提取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！