首页
\
问答
\
如何从SQL Server中的表查询Xml值和属性？(How to query for Xml values and attributes from table in SQL Server?)
如何从SQL Server中的表查询Xml值和属性？(How to query for Xml values and attributes from table in SQL Server?)
我有一个包含
Xml列的表：SELECT * FROM Sqm
一行
xml数据的示例将是：<Sqm version="1.2"> <Metrics> <Metric id="TransactionCleanupThread.RecordUsedTransactionShift" type="timer" unit="µs" count="1" sum="21490" average="21490" minValue="73701" maxValue="73701" >73701</Metric> <Metric id="TransactionCleanupThread.RefundOldTrans" type="timer" unit="µs" count="1" sum="184487" average="184487" minValue="632704" maxValue="632704" >632704</Metric> <Metric id="Database.CreateConnection_SaveContextUserGUID" type="timer" unit="µs" count="2" sum="7562" average="3781" minValue="12928" maxValue="13006" standardDeviation="16" >12967</Metric> <Metric id="Global.CurrentUser" type="timer" unit="µs" count="6" sum="4022464" average="670411" minValue="15" maxValue="13794345" standardDeviation="1642047">2299194</Metric> <Metric id="Global.CurrentUser_FetchIdentityFromDatabase" type="timer" unit="µs" count="1" sum="4010057" average="4010057" minValue="13752614" maxValue="13752614" >13752614</Metric> </Metrics> </Sqm>在这个数据的情况下，我想要：
SqmId id type unit count sum minValue maxValue standardDeviation Value ===== =================================================== ===== ==== ===== ====== ======== ======== ================= ====== 1 TransactionCleanupThread.RecordUsedTransactionShift timer µs 1 21490 73701 73701 NULL 73701 1 TransactionCleanupThread.RefundOldTrans timer µs 1 184487 632704 632704 NULL 632704 1 Database.CreateConnection_SaveContextUserGUID timer µs 2 7562 12928 13006 16 12967 1 Global.CurrentUser timer µs 6 4022464 15 13794345 1642047 2299194 1 Global.CurrentUser_FetchIdentityFromDatabase timer µs 1 4010057 13752614 13752614 NULL 13752614 2 ...最后我会实际执行
SUM()，MIN()，MAX()聚合。 但是现在我只是想查询 xml列。在伪代码中，我会尝试像：
SELECT SqmId, Data.query('/Sqm/Metrics/Metric/@id') AS id, Data.query('/Sqm/Metrics/Metric/@type') AS type, Data.query('/Sqm/Metrics/Metric/@unit') AS unit, Data.query('/Sqm/Metrics/Metric/@sum') AS sum, Data.query('/Sqm/Metrics/Metric/@count') AS count, Data.query('/Sqm/Metrics/Metric/@minValue') AS minValue, Data.query('/Sqm/Metrics/Metric/@maxValue') AS maxValue, Data.query('/Sqm/Metrics/Metric/@standardDeviation') AS standardDeviation, Data.query('/Sqm/Metrics/Metric') AS value FROM Sqm但是SQL查询不起作用：
消息2396，第16级，状态1，行2
XQuery [Sqm.data.query（）]：属性可能不会出现在元素之外我已经搜索过，令人惊奇的是，Xml查询是如何记录不好或者是示例的。 大多数资源而不是查询表 ，查询变量 ; 我没有做 大多数资源只使用xml查询进行过滤和选择，而不是读取值。 大多数资源读取硬编码的子节点（按索引），而不是实际值。
我看过的相关资源
- https://stackoverflow.com/questions/966441/xml-query-in-sql-server-2008
- SQL Server查询元素值的xml属性值
- SQL查询XML属性
- SQL Server 2005 XQuery和XML-DML - 第1部分
- BOL：Microsoft SQL Server 2005中的XML支持
- 在SQL Server中查询XML
- 基本SQL Server XML查询
- BOL：query（）方法（xml数据类型）
- XML Workshop V - 从XML列中读取值
- SQL SERVER - 发现XML数据类型方法 - 入门简介
更新：.value而不是.query
我尝试随机使用
.value，代替.query：SELECT Sqm.SqmId, Data.value('/Sqm/Metrics/Metric/@id', 'varchar(max)') AS id, Data.value('/Sqm/Metrics/Metric/@type', 'varchar(max)') AS type, Data.value('/Sqm/Metrics/Metric/@unit', 'varchar(max)') AS unit, Data.value('/Sqm/Metrics/Metric/@sum', 'varchar(max)') AS sum, Data.value('/Sqm/Metrics/Metric/@count', 'varchar(max)') AS count, Data.value('/Sqm/Metrics/Metric/@minValue', 'varchar(max)') AS minValue, Data.value('/Sqm/Metrics/Metric/@maxValue', 'varchar(max)') AS maxValue, Data.value('/Sqm/Metrics/Metric/@standardDeviation', 'varchar(max)') AS standardDeviation, Data.value('/Sqm/Metrics/Metric', 'varchar(max)') AS value FROM Sqm但这也不行：
Msg 2389，Level 16，State 1，Line 3 XQuery [Sqm.data.value（）]：
'value（）'需要一个单例（或空序列），找到的操作数类型为'xdt：untypedAtomic *'I have a table that contains a
Xmlcolumn:SELECT * FROM Sqm
A sample of the
xmldata of a row would be:<Sqm version="1.2"> <Metrics> <Metric id="TransactionCleanupThread.RecordUsedTransactionShift" type="timer" unit="µs" count="1" sum="21490" average="21490" minValue="73701" maxValue="73701" >73701</Metric> <Metric id="TransactionCleanupThread.RefundOldTrans" type="timer" unit="µs" count="1" sum="184487" average="184487" minValue="632704" maxValue="632704" >632704</Metric> <Metric id="Database.CreateConnection_SaveContextUserGUID" type="timer" unit="µs" count="2" sum="7562" average="3781" minValue="12928" maxValue="13006" standardDeviation="16" >12967</Metric> <Metric id="Global.CurrentUser" type="timer" unit="µs" count="6" sum="4022464" average="670411" minValue="15" maxValue="13794345" standardDeviation="1642047">2299194</Metric> <Metric id="Global.CurrentUser_FetchIdentityFromDatabase" type="timer" unit="µs" count="1" sum="4010057" average="4010057" minValue="13752614" maxValue="13752614" >13752614</Metric> </Metrics> </Sqm>In the case of this data, I would want:
SqmId id type unit count sum minValue maxValue standardDeviation Value ===== =================================================== ===== ==== ===== ====== ======== ======== ================= ====== 1 TransactionCleanupThread.RecordUsedTransactionShift timer µs 1 21490 73701 73701 NULL 73701 1 TransactionCleanupThread.RefundOldTrans timer µs 1 184487 632704 632704 NULL 632704 1 Database.CreateConnection_SaveContextUserGUID timer µs 2 7562 12928 13006 16 12967 1 Global.CurrentUser timer µs 6 4022464 15 13794345 1642047 2299194 1 Global.CurrentUser_FetchIdentityFromDatabase timer µs 1 4010057 13752614 13752614 NULL 13752614 2 ...In the end I'll actually be performing
SUM(),MIN(),MAX()aggregation. But for now I'm just trying to query an xml column.In pseudo-code, I would try something like:
SELECT SqmId, Data.query('/Sqm/Metrics/Metric/@id') AS id, Data.query('/Sqm/Metrics/Metric/@type') AS type, Data.query('/Sqm/Metrics/Metric/@unit') AS unit, Data.query('/Sqm/Metrics/Metric/@sum') AS sum, Data.query('/Sqm/Metrics/Metric/@count') AS count, Data.query('/Sqm/Metrics/Metric/@minValue') AS minValue, Data.query('/Sqm/Metrics/Metric/@maxValue') AS maxValue, Data.query('/Sqm/Metrics/Metric/@standardDeviation') AS standardDeviation, Data.query('/Sqm/Metrics/Metric') AS value FROM SqmBut that SQL query doesn't work:
Msg 2396, Level 16, State 1, Line 2
XQuery [Sqm.data.query()]: Attribute may not appear outside of an elementI've hunted, and it's amazing how poorly documented, or exampled, Xml querying is. Most resources rather than querying a table, query a variable; which I'm not doing. Most resources only use xml querying for filtering and selection, rather than reading values. Most resources read hard-coded child nodes (by index), rather than actual values.
Related resources that I read
- https://stackoverflow.com/questions/966441/xml-query-in-sql-server-2008
- SQL Server query xml attribute for an element value
- SQL querying XML attributes
- SQL Server 2005 XQuery and XML-DML - Part 1
- BOL: XML Support in Microsoft SQL Server 2005
- Querying XML in SQL Server
- Basic SQL Server XML Querying
- BOL: query() Method (xml Data Type)
- XML Workshop V - Reading Values from XML Columns
- SQL SERVER – Introduction to Discovering XML Data Type Methods – A Primer
Update: .value rather than .query
I tried randomly using
.value, in place of.query:SELECT Sqm.SqmId, Data.value('/Sqm/Metrics/Metric/@id', 'varchar(max)') AS id, Data.value('/Sqm/Metrics/Metric/@type', 'varchar(max)') AS type, Data.value('/Sqm/Metrics/Metric/@unit', 'varchar(max)') AS unit, Data.value('/Sqm/Metrics/Metric/@sum', 'varchar(max)') AS sum, Data.value('/Sqm/Metrics/Metric/@count', 'varchar(max)') AS count, Data.value('/Sqm/Metrics/Metric/@minValue', 'varchar(max)') AS minValue, Data.value('/Sqm/Metrics/Metric/@maxValue', 'varchar(max)') AS maxValue, Data.value('/Sqm/Metrics/Metric/@standardDeviation', 'varchar(max)') AS standardDeviation, Data.value('/Sqm/Metrics/Metric', 'varchar(max)') AS value FROM SqmBut that also doesn't work:
Msg 2389, Level 16, State 1, Line 3 XQuery [Sqm.data.value()]:
'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
原文：https://stackoverflow.com/questions/19165213
更新时间：2022-05-23 13:05
最满意答案
您的查询返回它应该返回的结果（无结果），因为您加入了
caballos.Cod字段，并且您将不同数量的Cod字段过滤为2.由于Cod字段是用于caballos表的唯一标识符，caballos它被使用在连接条件下，每个组的值不能超过1个。您可以在过滤条件中使用
Disciplina字段，因为它只是Disciplina字段，可能在结果集中的两个单独记录中具有不同的值：HAVING COUNT(DISTINCT d.Disciplina)=2Your query returns what it should (no result) because you join on the
caballos.Codfield, and you filter on the distinct number ofCodfields being 2. Since the Cod field sems to be a unique identifier forcaballostable and it is used in the join condition, it cannot have more than 1 distinct value per group.You could use the
Disciplinafield in the filter criteria instead because it is only theDisciplinafield that could have different values in 2 separate records in the resultset:HAVING COUNT(DISTINCT d.Disciplina)=2
相关问答
更多-
复合查询不支持括号，但子查询可以： SELECT a, b FROM table1 INTERSECT SELECT * FROM (SELECT a, b FROM table2 EXCEPT SELECT a, b FROM table3) 请注意， INTERSECT没有比EXCEPT更高的优先级， 实际规则是： 当三个或更多简单的SELECT连接到复合SELECT时，它们从左到右分组。 由于INTERSECT是可交换的，你可以简单地写下这 ...
-
MariaDB中的INTERSECT查询(INTERSECT query in MariaDB)[2024-02-20]
首先尝试这样，检查你是否拥有所有演员。 如果你有同名演员，我必须添加codactor。 SELECT a.codactor, a.nombre -- add ', *' to see all columns and test query is ok. FROM actores a JOIN participacion p ON a.codactor = p.actor JOIN peliculas m ON p.titulo = m.titulo AND p.ano = m.ano JOIN ... -
INTERSECT在更新方面没有任何意义。 您的SELECT查询实际上是同时运行的五个独立查询，以便返回五个独立结果集的交集。 由于UPDATE不生成结果集，因此尝试将其非结果与SELECT查询的结果相交是一个错误。 使用复杂的WHERE子句的单个SELECT查询似乎更好地服务于您的原始目的，因为五个查询中的每一个都针对检查不同条件的同一个表运行。 INTERSECT doesn't have any meaning in terms of updates. Your SELECT query is act ...
-
您的查询返回它应该返回的结果（无结果），因为您加入了caballos.Cod字段，并且您将不同数量的Cod字段过滤为2.由于Cod字段是用于caballos表的唯一标识符， caballos它被使用在连接条件下，每个组的值不能超过1个。 您可以在过滤条件中使用Disciplina字段，因为它只是Disciplina字段，可能在结果集中的两个单独记录中具有不同的值： HAVING COUNT(DISTINCT d.Disciplina)=2 Your query returns what it should ...
-
相交LINQ查询(Intersect LINQ query)[2022-11-11]
是。 正如其他人已经回答的那样，你可以使用Where ，但是对于大套装来说，效率将是非常低的。 如果性能受到关注，可以调用Join ： var results = original.Join(idsToFind, o => o.Id, id => id, (o, id) => o); 如果idsToFind可以包含重复项，则需要在ID或结果上调用Distinct() ，或者使用idsToFind替换Join （ GroupJoin的参数将相同）。 Yes. As other people have ans ... -
相反的相交（）(The opposite of Intersect())[2023-07-16]
如上所述，如果你想得到4的结果，你可以这样做： var nonintersect = array2.Except(array1); 如果你想要真正的非交叉（也是1和4），那么这应该是诀窍： var nonintersect = array1.Except(array2).Union( array2.Except(array1)); 这不是最有效的解决方案，但对于小列表，它应该可以正常工作。 As stated, if you want to get 4 as the result, you can do ... -
Mysql Intersect查询(Mysql Intersect query)[2024-02-14]
SELECT subjectid FROM exam_subjects WHERE examid IN (SELECT examid FROM exams) GROUP BY subjectid HAVING COUNT(*) = (SELECT COUNT(examid) FROM exams); 我在堆栈溢出的类似问题的帮助下得到了这个如何在组合表上进行交集 SELECT subjectid FROM exam_subjects WHERE examid IN (SELECT examid ... -
你想要做的是计算你的结果。 SELECT CASE WHEN COUNT(*) = 0 THEN 'Yes' ELSE 'No' END FROM ( SELECT CITY FROM DNAME WHERE DNAME='RESEARCH' INTERSECT SELECT CITY FROM DNAME WHERE DNAME='SCIENCE' ); 当然还有其他方法可以达到相同的效果。 这个演示了如何使用INTERSECT查询来完成它。 What you want to do is ...
-
相交的日期(Dates that intersect)[2022-04-12]
相交的日期： SELECT E.name, O.start_date, O.end_date FROM dbo.Names E INNER JOIN dbo.Dates O ON O.name = E.name AND O.start_date < @end_date AND O.end_date > @start_date WHERE E.name LIKE 'A' 不相交的日期与日期相反： SELECT E.name, O ... -
Linq to SQL和Intersect(Linq to SQL and Intersect)[2022-02-01]
这绝对是可能的。 看这个例子： http://msdn.microsoft.com/en-us/library/bb399392.aspx It is definitely possible. See this example: http://msdn.microsoft.com/en-us/library/bb399392.aspx