I am trying to extract values from a XML column in a table in SQL Server. I have this table InsuranceEntity with columns InsuranceEntity_ID and EntityXML.
The EntityXML column has values such as:
<insurance insurancepartnerid="CIGNA" sequencenumber="1"
subscriberidnumber="1234567" groupname="Orthonet-CIGNA"
groupnumber="7654321" copaydollaramount="1" />
How can I extract subscriberidnumber and groupnumber from this EntityXML column?
XQuery methods .nodes() and .value() to the rescue.
You may need to adjust data types. I used a generic VARCHAR(20) across the board.
SQL
--DDL and sample data population, start
DECLARE @tbl TABLE (InsuranceEntity_ID INT IDENTITY PRIMARY KEY, EntityXML XML);
INSERT INTO @tbl (EntityXML) VALUES
(N'<insurance insurancepartnerid="CIGNA" sequencenumber="1"
subscriberidnumber="1234567" groupname="Orthonet-CIGNA"
groupnumber="7654321" copaydollaramount="1"/>');
--DDL and sample data population, end
SELECT InsuranceEntity_ID
, c.value('@subscriberidnumber', 'VARCHAR(20)') AS subscriberidnumber
, c.value('@groupnumber', 'VARCHAR(20)') AS groupnumber
FROM @tbl
CROSS APPLY EntityXML.nodes('/insurance') AS t(c);
Output
+--------------------+--------------------+-------------+
| InsuranceEntity_ID | subscriberidnumber | groupnumber |
+--------------------+--------------------+-------------+
| 1 | 1234567 | 7654321 |
+--------------------+--------------------+-------------+