为什么这个存储过程只插入一个项目？

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.

好吧,所以,我是SQL Server 2008的新手但我的任务是创建一个程序,只需从某处加载一些XML文件,并将其名称和数据解析为一个双列表。

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

以下是给定文件内容的一般概念(尽管它包含大约100个实际条目):

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

对,够容易吧?我希望。以下脚本将运行整个文件(我知道这是因为我的文件在最后遇到了非法字符),但它不会创建一个包含多个条目的表(即它只插入XML文件的第一个条目)。我试过看看它是否是一个编码问题,通过将它保存在记事本以及Notepad ++中的不同内容......但是,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').

选择的内容始终是包含“毁灭之剑”的单行NAME和包含“+1 to Nubs”的DATA。当我调用它时,这个存储过程只会影响一行('exec TestParse')。

Any guidance here would be helpful.

这里的任何指导都会有所帮助。

1 个解决方案

#1

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

要获得2行,您必须将root / data传递给nodes()函数:

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

sql小提琴演示

#1