XPATH 查询，用于从存储在 SQL Server DB 列中的 XML 中获取数据

本文关键字：DB XML 数据 获取 Server SQL 查询 用于 存储 XPATH | 更新日期: 2023-09-27 18:36:22

我将 XML 数据存储在 SQL Server 数据库中的一列中。 

<data>
    <row>
<element name="product">Piston</element>
<element name="number">1.2</element>
    </row>
<row>
<element name="product">Piston Ring</element>
<element name="number">2</element>
    </row>
<row>
<element name="product">Piston</element>
<element name="number">1.5</element>
    </row>
</data>

有没有办法得到以下格式的结果？

------------------------------
Product    | Count
------------------------------
Piston     |   2
Piston Ring|   1
------------------------------

我尝试使用 Xpath，它给了我任何东西的计数，但不确定我是否可以按产品分组然后得到计数。

我正在寻找类似的东西（在SQL查询中）

SELECT Product, Count(Product) FROM ABC
GROUP BY Product

XPATH 查询，用于从存储在 SQL Server DB 列中的 XML 中获取数据

您不能直接在 GROUP BY 中使用 XML 方法，所以我会使用 CTE。首先，您从XML中获取表数据，然后可以使用GROUP BY执行"正常"COUNT：

DECLARE @xml XML=
'<data>
  <row>
    <element name="product">Piston</element>
    <element name="number">1.2</element>
  </row>
  <row>
    <element name="product">Piston Ring</element>
    <element name="number">2</element>
  </row>
  <row>
    <element name="product">Piston</element>
    <element name="number">1.5</element>
  </row>
</data>';
;WITH MyRows AS
(
    SELECT OneRow.value('(element[@name="product"])[1]','varchar(max)') AS Product
    FROM @xml.nodes('/data/row') AS A(OneRow)
)
SELECT Product,COUNT(Product) AS [Count]
FROM MyRows
GROUP BY Product

DECLARE @x XML=
'<data>
  <row>
    <element name="product">Piston</element>
    <element name="number">1.2</element>
  </row>
  <row>
    <element name="product">Piston Ring</element>
    <element name="number">2</element>
  </row>
  <row>
    <element name="product">Piston</element>
    <element name="number">1.5</element>
  </row>
</data>'
SELECT val, COUNT_BIG(1)
FROM (
    SELECT val = t.c.value('.', 'VARCHAR(100)')
    FROM @x.nodes('/data/row/element[@name="product"]') t(c)
) t
GROUP BY val
OPTION (OPTIMIZE FOR (@x = NULL))

输出- 

------------------- --------------------
Piston              2
Piston Ring         1

我会使用从table到XML列的CROSS APPLY

CREATE TABLE XMLwithOpenXML (
     id INT IDENTITY PRIMARY KEY
   , XMLData XML
   , LoadedDateTime DATETIME
)
GO
DECLARE @xml XML = '
<data> 
    <row>
        <element name="product">Piston</element>
        <element name="number">1.2</element>
    </row>
    <row>
        <element name="product">Piston Ring</element>
        <element name="number">2</element>
    </row>
    <row>
        <element name="product">Piston</element>
        <element name="number">1.5</element>
    </row>
</data>'
INSERT INTO XMLwithOpenXML (XMLData, LoadedDateTime)
SELECT @xml, GETDATE()
SELECT Product, COUNT(Product) AS ProdCount
FROM (
    SELECT
         n.C.value('(element[@name="product"])[1]', 'varchar(100)') product
       , n.C.value('(element[@name="number"])[1]', 'varchar(100)') number
    FROM XMLwithOpenXML
    CROSS APPLY XMLData.nodes('/data/row') n(C)
) A
GROUP BY Product
相关文章：