XPATH Query to get data from XML stored in a SQL Server DB column

Question

I have XML data stored in SQL Server database in one column.

<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>

Is there a way I can get result in following format?

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

I tried with Xpath which gives me count of anything, but not sure if I can group by product and then get a count.

I'm looking for something like (In SQL Query)

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

Gottfried Lesigang · Accepted Answer · 2016-02-26 07:33:38Z

5

You cannot use the XML methods directly within GROUP BYso I'd use a CTE. First you get table data out of your XML, than you can do a "normal" COUNT with GROUP BY:

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

answered Feb 26, 2016 at 7:33

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

A3006 Over a year ago

Hi, this is giving the expected result, but taking too much time. ( I checked with very large data). But thanks, it works.

Gottfried Lesigang Over a year ago

@Anand,you might think about an XML index. I answered a similar question shortly: stackoverflow.com/q/35575990/5089204

Mark Over a year ago

@Shnugo. Can you please explain how the following works? SELECT OneRow.value('(element[@name="product"])[1]','varchar(max)') AS Product FROM @xml.nodes('/data/row') AS A(OneRow)

Devart · Accepted Answer · 2016-02-26 08:36:33Z

1

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))

Output -

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

answered Feb 26, 2016 at 8:36

Devart

120k23 gold badges172 silver badges189 bronze badges

2 Comments

A3006 Over a year ago

This is brilliant! Thanks. Could you please explain me the last line? "OPTION (OPTIMIZE FOR (@x = NULL))"

Devart Over a year ago

Just a small optimization issue, if you expected a very big xml

Devart · Accepted Answer · 2016-02-26 08:41:00Z

I would use CROSS APPLY from table to XML column

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

Collectives™ on Stack Overflow

XPATH Query to get data from XML stored in a SQL Server DB column

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related