How to extract a xml string in strored procedure in sql 2005?

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[^]