代码之家 › 专栏 › 技术社区 › Dav.id

SQL 2008:获取与XML字段关联的行?

sql-server-2008 xml sql-server

Dav.id · 技术社区 · 16 年前

我想做的是:

我有一个名为CONTACTS的表,其中有一个名为memberID的主键字段

  <root><id>2</id><id>6</id><id>14</id></root>

因此,我试图通过一个存储过程来传递你的成员ID,并返回你所有的朋友信息,例如:

  select name, address, age, dob from contacts
  where id... xml join stuff...

我以前的工作方式(有点!)将所有XML节点(/root/id)选择到临时表中,然后从临时表连接到联系人表以获取联系人字段。。。

提前谢谢!

<--编辑--> 我确实得到了一些工作,但看起来像一个SQL弗兰肯斯坦语句! 基本上,我需要从XML字段获取friends contact ID,并填充到一个临时表中,如下所示:

Declare @contactIDtable TABLE (ID int)
INSERT INTO @contactIDtable (ID)
        SELECT CONVERT(INT,CAST(T2.memID.query('.') AS varchar(100))) AS friendsID
        FROM dbo.members
        CROSS APPLY memberContacts.nodes('/root/id/text()') AS T2(memID)

但是天哪!皈依者/演员的事情看起来很严重。。因为我需要为下一位获取一个INT,这是返回联系人数据的实际连接,如下所示:

SELECT memberID, memberName, memberAddress1
    FROM members
    INNER JOIN @contactIDtable cid
    ON members.memberID = cid.ID
    ORDER BY memberName

结果。。。好吧,它起作用了。。在我的例子中,我的memberContacts XML字段有3个节点(本例中是id),上面的查询返回3行数据(memberID、memberName、memberAddress1)。。。

还有其他想法/更有效的方法吗???

2 回复 | 直到 16 年前

Andomar 16 年前

sqlserver读取XML的语法是最不直观的语法之一。理想情况下,您应该:

select   f.name
from     friends f
join     @xml x
on       x.id = f.id

相反,SQLServer要求您详细说明所有内容。要将XML变量或列转换为“行集”,必须拼写出确切的路径,并想出两个别名:

@xml.nodes('/root/id') as table_alias(column_alias)

现在您必须向SQL Server解释如何 <id>1</id> 转换为int:

table_alias.column_alias.value('.', 'int')

所以您可以理解为什么大多数人喜欢在客户端解码XML:)

完整示例:

declare @friends table (id int, name varchar(50))
insert @friends (id, name)
          select  2, 'Locke Lamorra'
union all select  6, 'Calo Sanzo'
union all select 10, 'Galdo Sanzo'
union all select 14, 'Jean Tannen'

declare @xml xml
set @xml = ' <root><id>2</id><id>6</id><id>14</id></root>'

select  f.name
from    @xml.nodes('/root/id') as table_alias(column_alias)
join    @friends f
on      table_alias.column_alias.value('.', 'int') = f.id

marc_s MisterSmith 16 年前

.nodes() 在XML列上-类似于:

DECLARE @xmlfield XML
SET @xmlfield = '<root><id>2</id><id>6</id><id>14</id></root>'

SELECT
   ROOT.ID.value('(.)[1]', 'int')
FROM
   @xmfield.nodes('/root/id') AS ROOT(ID)

SELECT
    (list of fields)
FROM
    dbo.Contacts c
INNER JOIN
    @xmlfield.nodes('/root/id') AS ROOT(ID) ON c.ID = Root.ID.value('(.)[1]', 'INT')

基本上定义伪表 ROOT 只有一列 ID ,它将为XPath表达式中的每个节点包含一行 .value()