XML文件怎么导入到指定表中，并把列对应起来？

需要从XML文件定期更新数据库用户表的数据，怎么导入？用SQL语句还是management studio 附上一个XML数据
 <AccountsCompleteInfo_20150918>  
    <Person>
        <AccNum>103129</AccNum>
        <CARDID>-103129</CARDID>
        <CardCode>-103129</CardCode>
        <AccStatus>0</AccStatus>
        <AccType>1</AccType>
        <PerCode>-103129</PerCode>
        <AREANUM>1</AREANUM>
        <AccName>陈丹</AccName>
        <DEPNUM>150</DEPNUM>
        <DEPNAME>国商0471</DEPNAME>
        <CLSNUM>1</CLSNUM>
        <CLSNAME>学生</CLSNAME>
        <AccSex>女</AccSex>
        <mobileCode xml:space="preserve"> </mobileCode>
        <EMail xml:space="preserve"> </EMail>
        <PostCode xml:space="preserve"> </PostCode>
        <CertCode xml:space="preserve"> </CertCode>
        <CertTypeNum xml:space="preserve"> </CertTypeNum>
        <CertTypeName xml:space="preserve"> </CertTypeName>
        <ArcClassNum xml:space="preserve"> </ArcClassNum>
        <ArcClassName xml:space="preserve"> </ArcClassName>
        <NationNum xml:space="preserve"> </NationNum>
        <NationName xml:space="preserve"> </NationName>
        <PolityNum xml:space="preserve"> </PolityNum>
        <PolityName xml:space="preserve"> </PolityName>
        <DutyNum xml:space="preserve"> </DutyNum>
        <DutyName xml:space="preserve"> </DutyName>
        <TechNum xml:space="preserve"> </TechNum>
        <TechName xml:space="preserve"> </TechName>
        <PostNum xml:space="preserve"> </PostNum>
        <PostName xml:space="preserve"> </PostName>
        <EnterTime xml:space="preserve"> </EnterTime>
        <BirthDay xml:space="preserve"> </BirthDay>
        <PostDate>2006-09-05</PostDate>
        <LostDate>2010-09-04</LostDate>
    </Person>

24 个解决方案

#1

DECLARE @x XML= '<AccountsCompleteInfo_20150918>  
    <Person>
        <AccNum>103129</AccNum>
        <CARDID>-103129</CARDID>
        <CardCode>-103129</CardCode>
        <AccStatus>0</AccStatus>
        <AccType>1</AccType>
        <PerCode>-103129</PerCode>
        <AREANUM>1</AREANUM>
        <AccName>陈丹</AccName>
        <DEPNUM>150</DEPNUM>
        <DEPNAME>国商0471</DEPNAME>
        <CLSNUM>1</CLSNUM>
        <CLSNAME>学生</CLSNAME>
        <AccSex>女</AccSex>
        <mobileCode xml:space="preserve"> </mobileCode>
        <EMail xml:space="preserve"> </EMail>
        <PostCode xml:space="preserve"> </PostCode>
        <CertCode xml:space="preserve"> </CertCode>
        <CertTypeNum xml:space="preserve"> </CertTypeNum>
        <CertTypeName xml:space="preserve"> </CertTypeName>
        <ArcClassNum xml:space="preserve"> </ArcClassNum>
        <ArcClassName xml:space="preserve"> </ArcClassName>
        <NationNum xml:space="preserve"> </NationNum>
        <NationName xml:space="preserve"> </NationName>
        <PolityNum xml:space="preserve"> </PolityNum>
        <PolityName xml:space="preserve"> </PolityName>
        <DutyNum xml:space="preserve"> </DutyNum>
        <DutyName xml:space="preserve"> </DutyName>
        <TechNum xml:space="preserve"> </TechNum>
        <TechName xml:space="preserve"> </TechName>
        <PostNum xml:space="preserve"> </PostNum>
        <PostName xml:space="preserve"> </PostName>
        <EnterTime xml:space="preserve"> </EnterTime>
        <BirthDay xml:space="preserve"> </BirthDay>
        <PostDate>2006-09-05</PostDate>
        <LostDate>2010-09-04</LostDate>
    </Person>
	</AccountsCompleteInfo_20150918>  '

SELECT  t.c.value('AccNum[1]', 'int') AS AccNum
,t.c.value('AccName[1]', 'nvarchar(100)') AS AccName
,t.c.value('EMail[1]', 'nvarchar(100)') AS EMail
FROM    @x.nodes('AccountsCompleteInfo_20150918/Person') t ( c )

/*
AccNum	AccName	EMail
103129	陈丹	 */

按照上面方法一个个元素加上去显示就行了

#2

用xquery,或者openxml 去把节点的值拆分出来。插入到表里面就可以了

#3

这不是唯一的方法，仅供参考，我仅处理两个值，其他同理

DECLARE @xml XML
SET @xml=' <AccountsCompleteInfo_20150918>    
    <Person>
        <AccNum>103129</AccNum>
        <CARDID>-103129</CARDID>
        <CardCode>-103129</CardCode>
        <AccStatus>0</AccStatus>
        <AccType>1</AccType>
        <PerCode>-103129</PerCode>
        <AREANUM>1</AREANUM>
        <AccName>陈丹</AccName>
        <DEPNUM>150</DEPNUM>
        <DEPNAME>国商0471</DEPNAME>
        <CLSNUM>1</CLSNUM>
        <CLSNAME>学生</CLSNAME>
        <AccSex>女</AccSex>
        <mobileCode xml:space="preserve"> </mobileCode>
        <EMail xml:space="preserve"> </EMail>
        <PostCode xml:space="preserve"> </PostCode>
        <CertCode xml:space="preserve"> </CertCode>
        <CertTypeNum xml:space="preserve"> </CertTypeNum>
        <CertTypeName xml:space="preserve"> </CertTypeName>
        <ArcClassNum xml:space="preserve"> </ArcClassNum>
        <ArcClassName xml:space="preserve"> </ArcClassName>
        <NationNum xml:space="preserve"> </NationNum>
        <NationName xml:space="preserve"> </NationName>
        <PolityNum xml:space="preserve"> </PolityNum>
        <PolityName xml:space="preserve"> </PolityName>
        <DutyNum xml:space="preserve"> </DutyNum>
        <DutyName xml:space="preserve"> </DutyName>
        <TechNum xml:space="preserve"> </TechNum>
        <TechName xml:space="preserve"> </TechName>
        <PostNum xml:space="preserve"> </PostNum>
        <PostName xml:space="preserve"> </PostName>
        <EnterTime xml:space="preserve"> </EnterTime>
        <BirthDay xml:space="preserve"> </BirthDay>
        <PostDate>2006-09-05</PostDate>
        <LostDate>2010-09-04</LostDate>
    </Person>
    </AccountsCompleteInfo_20150918>'
    
    SELECT b.value('(AccNum)[1]','int') AS accnum ,b.value('(CARDID)[1]','varchar(100)')
    FROM @xml.nodes('AccountsCompleteInfo_20150918/Person') x(b)

#4

这个XML我就复制了一个学生的信息，大概有15000多个学生，每天一卡通服务器会把更新好数据的XML文件放到指定FTP上，我这边需要去下载这个XML文件，并对本地的用户表进行更新，是不是按照大神们发的代码做成SQL语句，放到计划任务里就可以了？

#5

如果是每天自动的，你可以用ssis里实现,遍历所有文件，然后逐个导入
还可以使用系统处理XML文件的存储过程，直接下载XML文件
看看下面有没有帮助
https://msdn.microsoft.com/en-us/library/ms191184.aspx
https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/

#6

这种我没有实际测试，也算是一种思路

INSERT INTO Person (accnum) 
SELECT b.value('(AccNum)[1]','int') AS accnum
FROM ( 
SELECT CAST(x AS XML)
FROM OPENROWSET(
     BULK 'C:\Person.xml',
     SINGLE_BLOB) AS T(x)
     ) AS T(x)
CROSS APPLY x.nodes('AccountsCompleteInfo_20150918/Person') AS X(b);

#7

那插到表中userid自动排序，但不能为空，这个怎么做？@ch21st 你下面这个SQL code 的话，只需要对应到原来表中执行就可以了吗

#8

引用 7 楼 nbjslz 的回复:

那插到表中userid自动排序，但不能为空，这个怎么做？@ch21st 你下面这个SQL code 的话，只需要对应到原来表中执行就可以了吗

加上ORDER BY 指定userid--对应显示列

你的数据量小，用#6方法可行，如果要效率直接用BULK INSERT 格式化导入就行了，用法可参照联机

#9

@roy_88 请问order by userid 对应哪个列呢? 还有表里有些字段不能为空，但XML文件里没有这些字段的定义，导入的时候就提示有些字段不能为空，终止导入？

#10

@roy_88   我写的sql 代码如下
DECLARE @idoc int;
DECLARE @doc xml;
SELECT @doc=BulkColumn FROM OPENROWSET(BULK N'D:\AccountsCompleteInfo_20150918.xml', SINGLE_BLOB) AS x

 
insert  into dbo.userinfo(Badgenumber,CardNo,Name,USERID) SELECT 
t.c.value('AccNum[1]', 'int') AS Badgenumber
,t.c.value('CARDID[1]', 'nvarchar(100)') AS CardNo
,t.c.value('AccName[1]', 'nvarchar(100)') AS Name
,t.c.value('PerCode[1]', 'nvarchar(100)') AS USERID


 FROM @doc.nodes('//Person') t ( c )

但写入表的速度真的好慢好慢。一共2万多条数据要半个多小时 

#11

引用 9 楼 nbjslz 的回复:

@roy_88 请问order by userid 对应哪个列呢? 还有表里有些字段不能为空，但XML文件里没有这些字段的定义，导入的时候就提示有些字段不能为空，终止导入？

在select位置把为null的转换空字符如： isnull(t.c.value('AccNum[1]', 'int') ,0) AS Badgenumber--int不能为NULL转为0

导入慢，看是赋值慢还是解析慢，可用openxml试试

#12

DECLARE @idoc int;
DECLARE @doc xml;
SELECT @doc=BulkColumn FROM OPENROWSET(BULK N'D:\AccountsCompleteInfo_20150918.xml', SINGLE_BLOB) AS x

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

insert  into dbo.userinfo(Badgenumber,CardNo,Name,USERID)
SELECT Badgenumber=ISNULL(Badgenumber,0),CardNo=ISNULL(CardNo,''),Name=ISNULL(Name,''),USERID=ISNULL(USERID,'')
FROM   OPENXML (@idoc, '//Person',3)
With(Badgenumber int 'AccNum[1]'
,CardNo nvarchar(100) 'CARDID[1]'
,Name nvarchar(100) 'AccName[1]'
,USERID nvarchar(100) 'PerCode[1]'
)

 
 EXEC sp_xml_removedocument @idoc;

#13

@ch21st 用您的SQL语句能执行，但导不进数据？

#14

@roy_88 opnxml 查询数据的时候大概15秒这样就能显示所有数据

#15

保存成SQL语句，能用批处理调用执行做成计划任务吗

#16

引用 15 楼 nbjslz 的回复:

保存成SQL语句，能用批处理调用执行做成计划任务吗

可以，看语句放在JOB里就行了，代理服务服务账号设置本地管理员或有权限访问硬盘路径的账号，才能执行

#17

@roy_88 像在批处理里调用 程序一样 start ""就可以吗？

#18

后来用OSQL能实现批处理执行了，谢谢各位大神，有什么问题还望指教

#19

引用 17 楼 nbjslz 的回复:

@roy_88 像在批处理里调用 程序一样 start ""就可以吗？

这需要用sqlcmd执行，可把语句存储为一个文件，用命令去执行这个文件

直接在SSMS作业里调度不是更方便

#20

还有个问题，有时候导入会提示数据类型为int 无法导入

#21

@roy_88 如果第二次开始只需要根据xml文件对表进行增加或删除，这upade语句怎么写呢？谢谢大神们

#22

@中国风 @道素 帮忙看下，能执行，但无法删除或增加

#23

代码如下，一个用户不能自己连续回帖3次以上。换了个用户
DECLARE @idoc int;
DECLARE @doc xml;
SELECT @doc=BulkColumn FROM OPENROWSET(BULK N'D:\AccountsBasicInfo_20150919.xml', SINGLE_BLOB) AS x
SELECT 
t.c.value('AccNum[1]', 'nvarchar(100)') AS Badgenumber
,t.c.value('CARDID[1]', 'nvarchar(100)') AS CardNo
,t.c.value('AccName[1]', 'nvarchar(100)') AS Name
,t.c.value('PerCode[1]', 'nvarchar(100)') AS USERID
 FROM @doc.nodes('//Person') t ( c )  
  /*****添加用户表中根据查询XML文件中需要增加的用户信息***************/
insert  into master.dbo.userinfo(Badgenumber,CardNo,Name,USERID)
SELECT Badgenumber=ISNULL(Badgenumber,0),CardNo=ISNULL(CardNo,''),Name=ISNULL(Name,''),USERID=ISNULL(USERID,'')
FROM   OPENXML (@idoc, '//Person',3)
With(Badgenumber nvarchar(100)  'AccNum[1]'
,CardNo nvarchar(100) 'CARDID[1]'
,Name nvarchar(100) 'AccName[1]'
,USERID nvarchar(100) 'PerCode[1]') 
select   t.c.value('CARDID[1]', 'nvarchar(100)'),dbo.USERINFO.CardNo 
  from @doc.nodes('//Person') t ( c ) ,dbo.USERINFO 
  where (dbo.USERINFO .CardNo  <> t.c.value('CARDID[1]', 'nvarchar(100)'));
  /*****删除用户表中根据查询XML文件中没有的用户信息***************/
 delete from dbo.USERINFO 
 where (not exists (select t.c.value('CARDID[1]', 'nvarchar(100)')
  from @doc.nodes('//Person') t ( c ) 
  where (dbo.USERINFO .CardNo  <> t.c.value('CARDID[1]', 'nvarchar(100)'))));
  EXEC sp_xml_removedocument @idoc;

#24

你可以先将数据存入到临时表中，然后再对数据进行处理，这样比对XML文件处理方便很多