请问存储过程怎么Load XML 文件数据到数据库表里?

Question

SELECT
author = B.c.value('author[1]', 'nvarchar(100)'),
articleid = B.c.value('articleid[1]', 'nvarchar(100)'),
timestamp = B.c.value('timestamp[1]', 'nvarchar(100)'),
source = B.c.value('source[1]', 'nvarchar(100)'),
corporatename = B.c.value('corporatename[1]', 'nvarchar(100)'),
asxcode = B.c.value('asxcode[1]', 'nvarchar(100)')
FROM(
SELECT
c = CONVERT(xml, T.c)
FROM OPENROWSET(BULK N'c:\xmlfile\afnsnews080924.xml', SINGLE_BLOB) T(c)
)A
CROSS APPLY A.nodes('/abixnews/news') B(c)

Answer 1

create procedure [dbo].[Pr_test]
     @object text
as
begin --创建临时表 批量处理XML 信息
create table #test 
( uid int identity(1,1) not null,
...
primary key (uid)
)        declare @idoc int
exec @ret = sp_xml_preparedocument @idoc OUTPUT, @object 
if (@ret = 0)
begin
--将xml中的信息复制到变量中
insert into #test (uid_id)
select uid...
from openxml(@idoc, '/businessinfo/rows/row/item/hs', 2)
with(uid varchar(8) '@id',
...) set @ret = @@error
if (@ret <> 0)
begin
return -1
end
        end       else
begin
raiserror('XML信息解析失败！', 16, 1)
return -1
end
exec sp_xml_removedocument @idoc

Answer 2

要把数据插入表, 则在上面的 SELECT 前面加 INSERT tb_name(author,articleid,timestamp,source,corporatename,asxcode)另外, 因为是用 sql 语句去 load 数据, 所以要求你的文件在 sql 服务器, 或者能从 sql 服务器访问的共享目录中

Answer 3

----------
上面的语句有错:
Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'BULK'.
--------------------
SELECT
    author = B.c.value('author[1]', 'nvarchar(100)'),
    articleid = B.c.value('articleid[1]', 'nvarchar(100)'),
    timestamp = B.c.value('timestamp[1]', 'nvarchar(100)'),
    source = B.c.value('source[1]', 'nvarchar(100)'),
    corporatename = B.c.value('corporatename[1]', 'nvarchar(100)'),
    asxcode = B.c.value('asxcode[1]', 'nvarchar(100)')    
FROM(
    SELECT
        c = CONVERT(xml, T.c)
    FROM OPENROWSET(BULK N'\\aufs1\ClientData-test\clientFTP\mymoney\afnsnews080924.xml', SINGLE_BLOB) T(c)
)A
    CROSS APPLY A.nodes('/abixnews/news') B(c)

Answer 4

 FROM OPENROWSET(BULK N'\\aufs1\ClientData-test\clientFTP\mymoney\afnsnews080924.xml', SINGLE_BLOB) T(c)  有问题吗!

Answer 5

--恩, 反正报错:
Incorrect syntax near the keyword 'BULK'.

Answer 6

SELECT 
    author = B.c.value('author[1]', 'nvarchar(100)'), 
    articleid = B.c.value('articleid[1]', 'nvarchar(100)'), 
    timestamp = B.c.value('timestamp[1]', 'nvarchar(100)'), 
    source = B.c.value('source[1]', 'nvarchar(100)'), 
    corporatename = B.c.value('corporatename[1]', 'nvarchar(100)'), 
    asxcode = B.c.value('asxcode[1]', 'nvarchar(100)')    
FROM( 
    SELECT 
        c = CONVERT(xml, T.c) 
    FROM OPENROWSET(BULK N'\aufs1\ClientData-test\clientFTP\mymoney\afnsnews080924.xml', SINGLE_BLOB) T(c) 
)A 
    CROSS APPLY c.nodes('/abixnews/news') B(c)
试一下这个

Answer 7

应该是c.nodes 不是A.nodes nodes方法针对列的吧

Answer 8

--------------------
没用,还是Incorrect syntax near the keyword 'BULK'.

Answer 9

SELECT 
    author = B.c.value('author[1]', 'nvarchar(100)'), 
    articleid = B.c.value('articleid[1]', 'nvarchar(100)'), 
    timestamp = B.c.value('timestamp[1]', 'nvarchar(100)'), 
    source = B.c.value('source[1]', 'nvarchar(100)'), 
    corporatename = B.c.value('corporatename[1]', 'nvarchar(100)'), 
    asxcode = B.c.value('asxcode[1]', 'nvarchar(100)')    
FROM( 
    SELECT 
        c = CONVERT(xml, T.c) 
    FROM OPENROWSET(BULK N'c:\xmlfile\afnsnews080924.xml', SINGLE_BLOB) T(c) 
)A 
    CROSS APPLY c.nodes('/abixnews/news') B(c) 
没看上面,不清楚你的文件路径!

Answer 10

-----------------------------
结论:
 这种写法在SQL 2000 中 不认? 老报错:Incorrect syntax near the keyword 'BULK';
 但是在SQL 2005 中可以执行,能取出数据来.请 邹建 解释一下了,我现在还是SQL2000的数据表啊! 这可怎么办?
         

Answer 11

DECLARE @idoc int
DECLARE @doc varchar(1000)
DECLARE @FileName varchar(255) 
DECLARE @ExecCmd VARCHAR(255) 
DECLARE @y INT 
DECLARE @x INT 
DECLARE @FileContents VARCHAR(8000) 
CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255)) 
SET @FileName = c:\xmlfile\afnsnews080924.xml'
SET @ExecCmd = 'type ' + @FileName 
SET @FileContents = '' 
INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd 
SELECT @y = count(*) from #tempXML 
SET @x = 0
WHILE @x <> @y 
BEGIN 
SET @x = @x + 1 
SELECT @FileContents = @FileContents + ThisLine from #tempXML WHERE PK 
= @x 
END 
SELECT @doc=@FileContents
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, '/abixnews/news',2)
            WITH ( author  nvarchar(10),
articleid nvarchar(20),
timestamp nvarchar(20),
source nvarchar(20),
corporatename nvarchar(20),
asxcode nvarchar(20))
EXEC sp_xml_removedocument @idoc
DROP TABLE #tempXML 
要是XML长度超过8000就不行了!
哪个高手解决一下超过8000的问题!

Answer 12

重来一下
DECLARE @idoc int
DECLARE @doc varchar(1000)
DECLARE @FileName varchar(255) 
DECLARE @ExecCmd VARCHAR(255) 
DECLARE @y INT 
DECLARE @x INT 
DECLARE @FileContents VARCHAR(8000) 
CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255)) 
SET @FileName = 'c:\xmlfile\afnsnews080924.xml'
SET @ExecCmd = 'type ' + @FileName 
SET @FileContents = '' 
INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd 
SELECT @y = count(*) from #tempXML 
SET @x = 0
WHILE @x <> @y 
BEGIN 
SET @x = @x + 1 
SELECT @FileContents = @FileContents + ThisLine from #tempXML WHERE PK 
= @x 
END 
SELECT @doc=@FileContents
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, '/abixnews/news',2)
            WITH (    author  nvarchar(10),
                    articleid nvarchar(20),
                    timestamp nvarchar(20),
                    source nvarchar(20),
                    corporatename nvarchar(20),
                    asxcode nvarchar(20))
EXEC sp_xml_removedocument @idoc
DROP TABLE #tempXML

Answer 13

这个我测试过,因为@FileContents 是空值.搞不懂.

Answer 14

declare @xmlfile varchar(20)
DECLARE @xmlFileName VARCHAR(300)
set @xmlfile  = 'afnsnews080924.xml'
SELECT @xmlFileName = '\\Szequitydata\dafiles\8K\'+@xmlfile
print 'path:'+@xmlFileName--INSERT into testxml(Date,author,articleid,timestamp,source,corporatename,asxcode,headline,fullstory) 
SELECT
    getdate(),
    author = B.c.value('author[1]', 'nvarchar(100)'),
    articleid = B.c.value('articleid[1]', 'nvarchar(100)'),
    timestamp = B.c.value('timestamp[1]', 'nvarchar(250)'),
    source = B.c.value('source[1]', 'nvarchar(250)'),
    corporatename = B.c.value('corporatename[1]', 'nvarchar(250)'),
    asxcode = B.c.value('asxcode[1]', 'nvarchar(6)'),
    headline = B.c.value('headline[1]','nvarchar(250)'),
    fullstory = B.c.value('fullstory[1]','nvarchar(4000)')  
FROM (
    SELECT
        c = CONVERT(xml, T.c)
    FROM OPENROWSET(BULK N'"+@xmlFileName+"',SINGLE_BLOB) T(c)
)A
      CROSS APPLY c.nodes('/abixnews/news') B(c)---------------------------------
为啥上面的路径换成变量@xmlFileName, 就不行了呢? 用绝对路径就可以.
Cannot bulk load because the file ""+@xmlFileName+"" could not be opened. Operating system error code 123(error not found).

Answer 15

------------------------------------
用动态解决了!use dynamic sql as suggested. something like:-SET @Sql='SELECT CAST(BulkColumn AS XML) FROM
openrowset(bulk N'''+@FilePath+''',
single_blob)'
EXEC (@Sql)

Answer 16

我的方法只适用于 sql 2005 及之后的版本

Answer 17

    FROM OPENROWSET(BULK N'c:\xmlfile\afnsnews080924.xml', SINGLE_BLOB) T(c)这部可否用变量来代替具体文件名地址, 因为想每次导入的xml文件名不同. 我试过用变量,但是说找不到文件, 但是换成具体string的文件名路径, 就工作. 
查了查 openrowset, 说是不支持virables in its arguments.不知道大家都怎么来动态输入xml文档用变量.