草庐IT

sql-server - SQL 2008 : getting rows with a join to an XML field?

coder 2024-07-03 原文

不确定这个问题是否会导致一些糟糕的表现，但现在似乎至少感觉是“更好的方法”..

我想做的是:

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

我还有一个 XML 字段，其中包含您 friend 的 ID(例如).. 就像:

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

所以我想通过存储过程做的是传入你的成员(member) ID，并返回你所有的 friend 信息，例如:

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

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

非常感谢任何帮助.. .query .nodes 示例只是有点过载，当然这可能是更好的方法...

提前致谢!

<-- 编辑="" --=""> 我确实得到了一些工作，但看起来像一个 SQL 科学怪人语句! 基本上我需要从 XML 字段中获取 friend 的联系 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)

但是很糟糕! convert/cast 看起来很严重.. 因为我需要为下一位获取一个 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)...

当然，这一切的重点是尽量避免创建一个多连接表，即我所有 friend ID 的列表......只是不确定上面是否真的让这更快更容易......

尝试执行此操作还有其他想法/更有效的方法吗？？？

最佳答案

SQL Server 用于读取 XML 的语法是最不直观的语法之一。理想情况下，您希望:

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

相反，SQL Server 要求您拼出所有内容。要将 XML 变量或列转换为“行集”，您必须拼出确切的路径并想出两个别名:

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

现在你必须向 SQL Server 解释如何转换 <id>1</id>变成一个整数:

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

关于sql-server - SQL 2008 : getting rows with a join to an XML field?，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/3381464/

sql-servergettingcode39prexmlsql-server-2008

有关sql-server - SQL 2008 : getting rows with a join to an XML field?的更多相关文章

  1. Hive SQL 五大经典面试题 - 2

    目录第1题连续问题分析：解法：第2题分组问题分析：解法：第3题间隔连续问题分析：解法：第4题打折日期交叉问题分析：解法：第5题同时在线问题分析：解法：第1题连续问题如下数据为蚂蚁森林中用户领取的减少碳排放量iddtlowcarbon10012021-12-1212310022021-12-124510012021-12-134310012021-12-134510012021-12-132310022021-12-144510012021-12-1423010022021-12-154510012021-12-1523.......找出连续3天及以上减少碳排放量在100以上的用户分析：遇到这类

  2. sql - 查询忽略时间戳日期的时间范围 - 2

    我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。 最佳答案 您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时

  3. sql - 在 Rails Console for PostgreSQL 的表中显示数据 - 2

    我找到了这样的东西:Rails:Howtolistdatabasetables/objectsusingtheRailsconsole?这一行没问题:ActiveRecord::Base.connection.tables并返回所有表但是ActiveRecord::Base.connection.table_structure("users")产生错误:ActiveRecord::Base.connection.table_structure("projects")我认为table_structure不是Postgres方法。如何列出Postgres数据库的Rails控制台中表中的所有

  4. ruby-on-rails - rails : uninitialized constant just happen on production server - 2

    我有一个放在lib/network中的类:moduleNetworkApiclassNetworkProxyendend然后在另一个类中，我引用了这个类:network_proxy=::NetworkApi::NetworkProxy.new(params)一切都在我的开发环境中正常运行，但是当我部署到服务器时，我在上面一行收到错误消息:NameError:uninitializedconstantNetworkApi::NetworkProxy我不知道为什么会出现这个奇怪的错误。请告诉我为什么。 最佳答案 请注意Rails5dis

  5. ruby - 防止SQL注入(inject)/好的Ruby方法 - 2

    Ruby中防止SQL注入(inject)的好方法是什么？ 最佳答案 直接使用ruby？使用准备好的语句:require'mysql'db=Mysql.new('localhost','user','password','database')statement=db.prepare"SELECT*FROMtableWHEREfield=?"statement.execute'value'statement.fetchstatement.close 关于ruby-防止SQL注入(inject

  6. ruby-on-rails - 如何在 Rails 中的不同数据库上执行直接 SQL 代码 - 2

    我正在编写一个Rails应用程序，它将监视某些特定数据库的数据质量。为了做到这一点，我需要能够对这些数据库执行直接SQL查询——这当然与用于驱动Rails应用程序模型的数据库不同。简而言之，这意味着我无法使用通过ActiveRecord基础连接的技巧。我需要连接的数据库在设计时是未知的(即:我不能将它们的详细信息放在database.yaml中)。相反，我有一个模型“database_details”，用户将使用它来输入应用程序将在运行时执行查询的数据库的详细信息。因此与这些数据库的连接实际上是动态的，细节仅在运行时解析。 最佳答案

  7. sql - Rails:使用 Postgres 创建对象时重复 ActiveRecord::RecordNotUnique？ - 2

    我正在使用Rails4应用程序，它需要创建大量对象以响应来自另一个系统的事件。当我调用create!时，主键列上出现非常频繁的ActiveRecord::RecordNotUnique错误(由PG::UniqueViolation引起)我的模型之一。我在SO上找到了其他答案，建议挽救异常并调用retry:beginTableName.create!(data:'here')rescueActiveRecord::RecordNotUnique=>eife.message.include?'_pkey'#Onlyretryprimarykeyviolationslog.warn"Retr

  8. ruby-on-rails - Ruby 和 SQL 中的重复业务逻辑 - 2

    我有一个PORO(普通旧Ruby对象)来处理一些业务逻辑。它接收一个ActiveRecord对象并对其进行分类。为了简单起见，以下面为例:classClassificatorSTATES={1=>"Positive",2=>"Neutral",3=>"Negative"}definitializer(item)@item=itemenddefnameSTATES.fetch(state_id)endprivatedefstate_idreturn1if@item.value>0return2if@item.value==0return3if@item.value但是，我还想根据这些st

  9. ruby-on-rails - Rails 和 MQTT : Subscribe to topic in background at server startup? - 2

    我想在服务器启动时在我的Rails应用程序中订阅一个mqtt主题，并保持订阅始终处于事件状态和运行状态。我正在使用这个mqttgem进行mqtt通信:https://github.com/njh/ruby-mqtt这是我现在拥有的:在application.rb中:config.after_initializedomqttSub=BackgroundMQTT.newmqttSub.runend后台MQTT类:classMQTTSubscriberdefrunThread.newdoMQTT::Client.connect(:host=>'localhost',:port=>1883,)

  10. sql - ActiveRecord 的意外行为包括 - 2

    我正在使用ARincludes在对象User和Building之间执行LEFTOUTERJOIN的方法，其中User可能有也可能没有Building关联:users=User.includes(:building).references(:buildings)因为我正在使用references，任何关联的Building对象都将被预先加载。我的期望是我随后能够遍历用户列表，并检查用户是否有与其关联的建筑物而不会触发额外的查询，但实际上每当我尝试访问建筑物属性时我都会看到对于没有建筑物的用户，AR会进行另一个SQL调用以尝试检索该建筑物(尽管在后续尝试中它只会返回nil)。这些查询显然是

随机推荐