Why is this stored procedure only inserting one item?

Question

Okay, so, I'm very new to SQL Server 2008 But my mission is to create a procedure that will simply load in some XML file from somewhere and parse its name and data into a single two-column table.

Here's a general idea of what the contents of a given file will look like (though it contains about 100 actual entries):

<root>
<data name="Sword of Doom" xml:space="preserve"><value>+1 to Nubs</value></data>
<data name="Sword of Doom+1" xml:space="preserve"><value>+2 to Nubs</value></data>
</root>

Right, easy enough, right? I wish. The following script will it will run through the entire file (I know this because my file had an illegal character it encountered towards the end), but it WON'T create a table with more than one entry in it (namely, it only inserts the first entry of the XML file). I've tried seeing if it's an encoding issue, by saving it in notepad as well as Notepad++ under different things... but, nope.

I don't understand, what am I doing wrong here? Is it with how I'm bulk loading?

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

   CREATE PROCEDURE TestParse AS
    BEGIN
    IF OBJECT_ID('dbo.TRANS', 'U') IS NOT NULL
        DROP TABLE TRANS
    DECLARE @XmlFile XML
    SELECT @XmlFile = BulkColumn
    FROM OPENROWSET(BULK 'C:/TestTest.xml', SINGLE_BLOB) as x

    CREATE TABLE dbo.TRANS
    (
        NAME VARCHAR(255),
        DATA VARCHAR(255)
    )
    BEGIN
        INSERT INTO TRANS
        (
            NAME,
            DATA
        )
        SELECT
            x.TRANS.value('(/root)[1]','varchar(255)') AS DATA,
            x.TRANS.value('(/root/data/@name)[1]', 'varchar(255)') AS NAME
        FROM @XmlFile.nodes('/root') AS x (TRANS)
    END
    select * from TRANS
END
GO

The contents of the selection is always a single row NAME containing 'Sword of Doom' and DATA containing '+1 to Nubs'. Only one row is ever effected by this stored procedure when I call it ('exec TestParse').

Any guidance here would be helpful.

Answer 1

To get 2 rows you have to pass root/data into nodes() function:

select
    x.TRANS.value('(value/text())[1]','varchar(255)') as DATA,
    x.TRANS.value('@name', 'varchar(255)') as NAME
from @XmlFile.nodes('/root/data') as x(TRANS)

sql fiddle demo