有个4GB大的xml数据文件,里面有多个字段,如何把它保存到数据库里。各字段各成一列呢?求助
解决方案 »
- 求助,关于数据库表格设计
- mysql连接tomcat问题
- mysql触发器
- mysql4.0拷贝到MYSQL5.0显示乱码
- 做了一个全文检索,基数显示是1,正常不?
- 高分请教:批量删除SQL语句
- 在linux中安装mysql出现的问题,请高人指教
- 利用struts框架,向mysql中写入中文的时候出现乱码问题???
- 急。。再线等待。。求一个MYSQL的查询语句。谢谢!
- 80分,我想取得刚存入表中记录的id号?如何做,该id是auto_increment,所以只好等存入后再取出,最正确的最法?
- 跪求navicat for mysql 11.1.13注册码
- PowerDesigner 修改表名,更新到数据库失败
Examples of Bulk Import and Export of XML Documents (SQL Server)如果进一步需要帮助,最好能把xml文件的数据格式贴出来
<?xml version="1.0" encoding="utf-8" ?>
- <!-- Prosper Data Export - Data - Version: 1.0 - Generated: 2012-02-14T03:42:14 -->
- <!-- Subject to the Prosper API Services Terms of Use Agreement located at http://www.prosper.com/tools/APITermsOfUse.aspx -->
- <ProsperDataExport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="ProsperDataExport.xsd">
- <Bids>
- <Bid>
<Amount>50.00</Amount>
<CreationDate>2006-02-21T20:23:20</CreationDate>
<Key>0F12336634427919902FE93</Key>
<ListingKey>D6473365888221963456102</ListingKey>
<ListingStatus>Completed</ListingStatus>
<MemberKey>F4273365023760472DBD934</MemberKey>
<MinimumRate>0.25000</MinimumRate>
<MinimumYield>0.24500</MinimumYield>
<ParticipationAmount>0.00</ParticipationAmount>
<Status>Outbid</Status>
<ModifiedDate>2006-02-21T20:23:20</ModifiedDate>
</Bid>
- <Bid>
<Amount>50.00</Amount>
<CreationDate>2006-02-21T22:50:00</CreationDate>
<Key>87F333655285187004B2EE8</Key>
<ListingKey>D6473365888221963456102</ListingKey>
<ListingStatus>Completed</ListingStatus>
<MemberKey>F4273365023760472DBD934</MemberKey>
<MinimumRate />
<MinimumYield />
<ParticipationAmount>50.00</ParticipationAmount>
<Status>Winning</Status>
<ModifiedDate>2006-02-21T22:50:00</ModifiedDate>
</Bid>这是.xml文件中其中的2条数据,怎么弄呢?
<!-- Prosper Data Export - Data - Version: 1.0 - Generated: 2012-02-14T03:42:14 -->
<!-- Subject to the Prosper API Services Terms of Use Agreement located at http://www.prosper.com/tools/APITermsOfUse.aspx -->
<ProsperDataExport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="ProsperDataExport.xsd">
<Bids>
<Bid>
<Amount>50.00</Amount>
<CreationDate>2006-02-21T20:23:20</CreationDate>
<Key>0F12336634427919902FE93</Key>
<ListingKey>D6473365888221963456102</ListingKey>
<ListingStatus>Completed</ListingStatus>
<MemberKey>F4273365023760472DBD934</MemberKey>
<MinimumRate>0.25000</MinimumRate>
<MinimumYield>0.24500</MinimumYield>
<ParticipationAmount>0.00</ParticipationAmount>
<Status>Outbid</Status>
<ModifiedDate>2006-02-21T20:23:20</ModifiedDate>
</Bid>
<Bid>
<Amount>50.00</Amount>
<CreationDate>2006-02-21T22:50:00</CreationDate>
<Key>87F333655285187004B2EE8</Key>
<ListingKey>D6473365888221963456102</ListingKey>
<ListingStatus>Completed</ListingStatus>
<MemberKey>F4273365023760472DBD934</MemberKey>
<MinimumRate/>
<MinimumYield/>
<ParticipationAmount>50.00</ParticipationAmount>
<Status>Winning</Status>
<ModifiedDate>2006-02-21T22:50:00</ModifiedDate>
</Bid>
</Bids>
</ProsperDataExport>
'SELECT t.c.value('(Amount/text())[1]','decimal(18,2)')
,t.c.value('(CreationDate/text())[1]','datetime')
,t.c.value('(Key/text())[1]','varchar(50)')
,t.c.value('(ListingKey/text())[1]','varchar(50)')
,t.c.value('(ListingStatus/text())[1]','varchar(50)')
,t.c.value('(MemberKey/text())[1]','varchar(50)')
,t.c.value('(MinimumRate/text())[1]','decimal(18,5)')
,t.c.value('(MinimumYield/text())[1]','decimal(18,5)')
,t.c.value('(ParticipationAmount/text())[1]','decimal(18,2)')
,t.c.value('(Status/text())[1]','varchar(50)')
,t.c.value('(ModifiedDate/text())[1]','datetime')
FROM @x.nodes('ProsperDataExport/Bids/Bid')t(c)/*
(无列名) (无列名) (无列名) (无列名) (无列名) (无列名) (无列名) (无列名) (无列名) (无列名) (无列名)
50.00 2006-02-21 20:23:20.000 0F12336634427919902FE93 D6473365888221963456102 Completed F4273365023760472DBD934 0.25000 0.24500 0.00 Outbid 2006-02-21 20:23:20.000
50.00 2006-02-21 22:50:00.000 87F333655285187004B2EE8 D6473365888221963456102 Completed F4273365023760472DBD934 NULL NULL 50.00 Winning 2006-02-21 22:50:00.000
*/
把这么大的文件导入到sql数据库 速度会很慢的,为什么一定要存在数据库呢? 还有这个文件为什么会有4G那么大
想要边读取.xml文件的数据,边插入数据库的表里面,应该怎么做呢?
当然也可以插入excel表或者其他,存在哪里速度会快呢?
这是.xml数据链接: https://pan.baidu.com/s/1eSfwQFg 密码: 5csf
在压缩包里
用以下方法赋值,试试DECLARE @x XMLSELECT @x=CONVERT(xml, BulkColumn, 2) FROM
OPENROWSET(Bulk 'D:\路径\XmlFile.xml', SINGLE_BLOB) [rowsetresults]SELECT @x--不能读时直接用XML格式化导入
另外还有一个.xml大的数据文件,但这个xml里含有多个表,这就导入不进数据库去了,这改怎么办呢?