Hi,
It's not so hard to do that in the
SQL Server. According to your example:
DECLARE @XML XML;
SET @XML = '<root>
<element0> dummy data 0</element0>
<element1> dummy data 1</element1>
</root>';
A couple of examples:
1. Using nodes() method.
SELECT T.c.value('element0[1]', 'varchar(250)') AS element0,
T.c.value('element1[1]', 'varchar(250)') AS element1
FROM @XML.nodes('root') AS T(c);
2. Using nodes() method, CTE and variables (to set values).
DECLARE @element0 VARCHAR(250),
@element1 VARCHAR(250);
WITH CTE
AS (SELECT c.value('element0[1]', 'varchar(250)') AS element0,
c.value('element1[1]', 'varchar(250)') AS element1
FROM @XML.nodes('root') AS T(c))
SELECT @element0 = element0,
@element1 = element1
FROM CTE;
SELECT @element0 AS element0,
@element1 AS element1;
Result:
element0 element1
-----------------------------
dummy data 0 dummy data 1
You can find more information here:
1.
SQL SERVER – Simple Example of Reading XML File Using T-SQL[
^]
2.
XML String read then insert into SQL Server table[
^]