SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable
    (Column1 varchar(100))
;

INSERT INTO mytable
    (Column1)
VALUES
('<Xml><Description /><BillingCode>Billing1</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing12</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>TEST</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>BillingCodeHere</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing3</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>123</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing2</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>12345</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing1</BillingCode></Xml>'),
('<Xml><Description>Line 1</Description><BillingCode>BillingCodeHere</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing1</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing2</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing1</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>SomethingHere</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing2</BillingCode></Xml>')
15 rows affected

SELECT BillingCode, COUNT(*)
FROM  mytable
CROSS APPLY (SELECT CAST(Column1 AS XML)) u(v)
CROSS APPLY u.v.nodes('//BillingCode') s(c)
CROSS APPLY (SELECT s.c.value('.', 'nvarchar(128)')) AS z(BillingCode)
GROUP BY BillingCode


BillingCode
(No column name)


BillingCodeHere
2

Billing1
4

TEST
1

Billing2
3

12345
1

Billing3
1

SomethingHere
1

Billing12
1

123
1

BillingCode	(No column name)
BillingCodeHere	2
Billing1	4
TEST	1
Billing2	3
12345	1
Billing3	1
SomethingHere	1
Billing12	1
123	1