Xml and MS SQL Server 2019, insert

I am connecting to a Tcp socket and a receive xml feeds. for an example lets say the xml is like the follow.

  <?xml version="1.0" encoding="UTF-8"?>
       <games>
           <game id="1000" name="warthunder"  score="987610" rank="1" users_online="17625"/>
           <game id="1001" name="american pool"  score="187610" rank="2" users_online="1122"/>
       ......
      ......
        <game id="2000" name="our world"  score="7610" rank="2000" users_online="37"/>
 </games>

I receive this every 2-3 seconds and at the moment I store it in the database in an XML column.

So on my front end I read this column from the sql as XML and I parse it.

I would prefer to have all XML attributes stored as separate columns so it would be no need to parse the XML. But then I need to to do 2000 Insert/Updates each time when I receive the XML.

Is there a way to shred the XML and insert into separate columns in SQL Server?

thank you.

Solution

It is easy to implement by using XML data type .nodes() method. It allows to shred XML and convert it into a rectangular format.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, game_id INT, [name] VARCHAR(30), score INT, [rank] INT, users_online INT);

DECLARE @xml XML = 
'<?xml version="1.0" encoding="UTF-8"?>
<games>
    <game id="1000" name="warthunder" score="987610" rank="1" users_online="17625"/>
    <game id="1001" name="american pool" score="187610" rank="2" users_online="1122"/>
    <game id="2000" name="our world" score="7610" rank="2000" users_online="37"/>
</games>';
-- DDL and sample data population, end

INSERT INTO @tbl (game_id,[name],score,[rank],users_online)
SELECT c.value('@id','INT') AS game_id
    , c.value('@name','VARCHAR(30)') AS [name]
    , c.value('@score','INT') AS score
    , c.value('@rank','INT') AS [rank]
    , c.value('@users_online','INT') AS users_online
FROM @xml.nodes('/games/game') AS t(c);

-- test
SELECT * FROM @tbl;

Output

+----+---------+---------------+--------+------+--------------+
| ID | game_id |     name      | score  | rank | users_online |
+----+---------+---------------+--------+------+--------------+
|  1 |    1000 | warthunder    | 987610 |    1 |        17625 |
|  2 |    1001 | american pool | 187610 |    2 |         1122 |
|  3 |    2000 | our world     |   7610 | 2000 |           37 |
+----+---------+---------------+--------+------+--------------+