Xml and MS SQL Server 2019, insert

Question

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.

Answer 1

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