辣手的XML,如何将sql server批量导出到xml?

emailqjc 2009-07-25 03:41:37
如何将sql server批量导出到xml?
我想从SQL数据库导出数据到XML的格式如下:
<?xml version="1.0" encoding="gb2312" ?>
<ecsjsjhtxx SJBH="0000010101" GHSBH="000001" HTLSH="1" HTLXID="01" WRITERQ="2002-01-25T00:00:00" HTSTARTRQ="2007-09-14T23:19:00" HTENDRQ="2015-12-31T00:00:00" JXLXID="01" SHFSID="01" DHFSID="01" JSFSID="05" NeedTax="NO" NeedKCBZ="T" JSZQ="30" KDLXID="01" KDL="0.000000" ArriveDay="3" KDFY="0.0000" BDJE="0.0000" ZGBHID="0000" USEMJ="0.00" APeople="0" BPeople="0" BDMODE="T" MonthRent="0.0000" RentBail="0.0000" CashType="UN" AWRITER="甲方" BWRITER="已方" CEKUSERID="00000" CHECKRQ="2007-09-14T23:19:00" HTNOTE="" CONTINUENO="0" SCJSRQ="2007-09-14T23:19:26.797" ZTBZ="T" ZTRQ="2008-12-15T14:04:26.357" ZTSM="" USERID="00000" REMARK="" />
<ecsjsjhtxx SJBH="0000010102" GHSBH="000001" HTLSH="2" HTLXID="01" WRITERQ="2002-01-25T00:00:00" HTSTARTRQ="2007-10-05T00:00:00" HTENDRQ="2007-12-31T00:00:00" JXLXID="01" SHFSID="01" DHFSID="01" JSFSID="05" NeedTax="NO" NeedKCBZ="T" JSZQ="30" KDLXID="01" KDL="0.000000" ArriveDay="3" KDFY="0.0000" BDJE="0.0000" ZGBHID="0000" USEMJ="0.00" APeople="0" BPeople="0" BDMODE="T" MonthRent="0.0000" RentBail="0.0000" CashType="UN" AWRITER="ZHY" BWRITER="YY" HTNOTE="" CONTINUENO="0" SCJSRQ="2007-10-04T21:04:29.920" ZTBZ="T" ZTRQ="2007-11-03T16:12:10.873" USERID="00004" REMARK="不再使用" />
<ecsjsjhtxx SJBH="0000020101" GHSBH="000002" HTLSH="1" HTLXID="01" WRITERQ="2006-12-11T14:44:00" HTSTARTRQ="2007-09-14T23:19:00" HTENDRQ="2015-12-31T00:00:00" JXLXID="01" SHFSID="01" DHFSID="01" JSFSID="05" NeedTax="NO" NeedKCBZ="T" JSZQ="30" KDLXID="01" KDL="0.000000" ArriveDay="3" KDFY="0.0000" BDJE="0.0000" ZGBHID="0000" USEMJ="0.00" APeople="0" BPeople="0" BDMODE="T" MonthRent="0.0000" RentBail="0.0000" CashType="UN" AWRITER="甲方" BWRITER="已方" CEKUSERID="00000" CHECKRQ="2007-09-14T23:19:00" HTNOTE="" CONTINUENO="0" SCJSRQ="2007-09-14T23:19:26.797" ZTBZ="T" ZTRQ="2008-12-15T14:04:26.357" ZTSM="" USERID="00000" REMARK="" />

也就是说每一条记录集就是一条,可是怎么总是不对
...全文
446 19 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
19 条回复
切换为时间正序
请发表友善的回复…
发表回复
wadotnet 2009-10-13
  • 打赏
  • 举报
回复
我也想知道,但是不会呀!急!我着急!更急想帮帮你。。。
emailqjc 2009-07-27
  • 打赏
  • 举报
回复
1、有一表结构如下:
create table et_order_message
(
MsgTitle varchar(20),
MsgContent varchar(20),
UserID varchar(20),
OrderID varchar(20),
INsertTime varchar(20),
IsRead varchar(20),
MsgType varchar(20),
MsgEx varchar(20),
IsCancel varchar(20)
)

2、向表里面写数据:
insert into et_order_message
select '99','0','10000440','495434','2000/1/1 0:00:00',0,0,'',0
union all
select '99','0','10000440','495434','2000/1/1 0:00:00',0,0,'',0
3、我产生XML的SQL语句如下:
select 1 AS Tag,
NULL AS Parent,
null as 'Msg!1!',
null as 'Item!2!id',
null as 'Item!2!MsgContent!Element',
null as 'Item!2!UserID!Element',
null as 'Item!2!OrderID!Element',
null as 'Item!2!INsertTime!Element',
null as 'Item!2!IsRead!Element',
null as 'Item!2!MsgType!Element',
null as 'Item!2!MsgEx!Element',
null as 'Item!2!IsCancel!Element'
from et_order_message
union all
select 2 as Tas,
1 as Parent,
'',
'',
MsgContent,UserID,OrderID,INsertTime,IsRead,MsgType,MsgEx,IsCancel
from et_order_message
FOR XML EXPLICIT

4、产生的结果如下:
<Msg />
<Msg />
<Msg />
<Msg>
<Item id="">
<MsgContent>0</MsgContent>
<UserID>10000440</UserID>
<OrderID>495434</OrderID>
<INsertTime>2000/1/1 0:00:00</INsertTime>
<IsRead>0</IsRead>
<MsgType>0</MsgType>
<MsgEx></MsgEx>
<IsCancel>0</IsCancel>
</Item>
<Item id="">
<MsgContent>0</MsgContent>
<UserID>10000440</UserID>
<OrderID>495434</OrderID>
<INsertTime>2000/1/1 0:00:00</INsertTime>
<IsRead>0</IsRead>
<MsgType>0</MsgType>
<MsgEx></MsgEx>
<IsCancel>0</IsCancel>
</Item>
<Item id="">
<MsgContent>0</MsgContent>
<UserID>10000440</UserID>
<OrderID>495434</OrderID>
<INsertTime>2000/1/1 0:00:00</INsertTime>
<IsRead>0</IsRead>
<MsgType>0</MsgType>
<MsgEx></MsgEx>
<IsCancel>0</IsCancel>
</Item>
<Item id="">
<MsgContent>0</MsgContent>
<UserID>10000440</UserID>
<OrderID>495434</OrderID>
<INsertTime>2000/1/1 0:00:00</INsertTime>
<IsRead>0</IsRead>
<MsgType>0</MsgType>
<MsgEx></MsgEx>
<IsCancel>0</IsCancel>
</Item>
</Msg>

请问我怎么去掉最上面的:
<Msg />
<Msg />
<Msg />
<Msg>
emailqjc 2009-07-27
  • 打赏
  • 举报
回复
不是哪个问题,生成XML必须为1
guguda2008 2009-07-27
  • 打赏
  • 举报
回复
SELECT 1改成SELECT DISINTCT 1试试
emailqjc 2009-07-27
  • 打赏
  • 举报
回复
急。。。
emailqjc 2009-07-27
  • 打赏
  • 举报
回复
朋友们捧捧场塞
kkun_3yue3 2009-07-25
  • 打赏
  • 举报
回复
导出XML
示例供参考
/*
Borrowed from Kent's code
*/

declare @agent table
(
AgentID int,
Fname varchar(5),
SSN varchar(11)
)

insert into @agent
select 1, 'Vimal', '123-23-4521' union all
select 2, 'Jacob', '321-52-4562' union all
select 3, 'Tom', '252-52-4563'

declare @address table
(
AddressID int,
AddressType varchar(12),
Address1 varchar(20),
Address2 varchar(20),
City varchar(25),
AgentID int
)

insert into @address
select 1, 'Home', 'abc', 'xyz road', 'RJ', 1 union all
select 2, 'Office', 'temp', 'ppp road', 'RJ', 1 union all
select 3, 'Home', 'xxx', 'aaa road', 'NY', 2 union all
select 4, 'Office', 'ccc', 'oli Com', 'CL', 2 union all
select 5, 'Temp', 'eee', 'olkiu road', 'CL', 2 union all
select 6, 'Home', 'ttt', 'loik road', 'NY', 3

/*
End Borrow
*/

SELECT
Tag,
Parent,
[Agents!1!],
[Agent!2!AgentID],
[Agent!2!Fname!Element],
[Agent!2!SSN!Element],
[AddressCollection!3!Element]
FROM (
SELECT
1 AS Tag,
NULL AS Parent,
0 AS Sort,
NULL AS 'Agents!1!',
NULL AS 'Agent!2!AgentID',
NULL AS 'Agent!2!Fname!Element',
NULL AS 'Agent!2!SSN!Element',
NULL AS 'AddressCollection!3!Element'
UNION ALL
SELECT
2 AS Tag, 1 AS Parent,
AgentID * 100 AS Sort,
NULL, AgentID, Fname, SSN,
NULL
FROM @agent
UNION ALL
SELECT
3 AS Tag, 2 AS Parent,
AgentID * 100 + 1 AS Sort,
NULL, NULL, NULL, NULL,
NULL
FROM @agent
) A
ORDER BY Sort
FOR XML EXPLICIT


导入,供参考
alter Procedure Msg_ET_Order_Message_Batch_Insert(
@MsgArray xml
)
as
begin
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @MsgArray;
INSERT INTO et_order_message
SELECT
MsgTitle 'MsgTitle',
REPLACE(MsgContent,'UserName',ISNULL(NULLIF(Cms_Member.UserName,''),'会员')) 'MsgContent',
UserID 'UserID',
OrderID 'OrderID',
INsertTime 'INsertTime',
IsRead 'IsRead',
MsgType 'MsgType',
MsgEx 'MsgEx',
IsCancel 'IsCancel'
FROM OPENXML(@docHandle, N'/Msg/Item',2)
WITH et_order_message LEFT JOIN CMS_MEMBER
ON UserID = CMS_MEMBER.T_ID
EXEC sp_xml_removedocument @docHandle
end

/*
参考示例
<?xml version="1.0" encoding="gb2312"?>
<Msg>
<Item>
<MsgTitle>7</MsgTitle>
<MsgContent>0</MsgContent>
<UserID>10000440</UserID>
<OrderID>495434</OrderID>
<INsertTime>2000/1/1 0:00:00</INsertTime>
<IsRead>0</IsRead>
<MsgType>0</MsgType>
<MsgEx>
</MsgEx>
<IsCancel>0</IsCancel>
</Item>
<Item>
<MsgTitle>7</MsgTitle>
<MsgContent>0</MsgContent>
<UserID>10000440</UserID>
<OrderID>495434</OrderID>
<INsertTime>2000/1/1 0:00:00</INsertTime>
<IsRead>0</IsRead>
<MsgType>0</MsgType>
<MsgEx>
</MsgEx>
<IsCancel>0</IsCancel>
</Item>
</Msg>
*/
xie_yanke 2009-07-25
  • 打赏
  • 举报
回复
主题回答完毕。

你的这个导入导出数据是另外的问题。:D
emailqjc 2009-07-25
  • 打赏
  • 举报
回复
我再说一下我想最终达到的目的:
1 从SQL数据库将数据导出到XML
2 从XML将数据导入SQL数据库

你们有没有完美的解决方案,听听你们的高见,我都不知道什么原因,我昨天明明都测试了的,今天突然就不对了
xie_yanke 2009-07-25
  • 打赏
  • 举报
回复
跟你说过了,如果你非要增加回车符,一行一行的显示, 也告诉你了。(6,7楼)

而对于xml来说,回车无意义。
wujinjian2008n 2009-07-25
  • 打赏
  • 举报
回复
select * from Emp for xml raw('hang'),elements,root('root')
emailqjc 2009-07-25
  • 打赏
  • 举报
回复
我是要求导出到XML 后文件打开,的效果如下:
<ecsjsjhtxx sjbh="0000010101" ghsbh="000001"/>
<ecsjsjhtxx sjbh="0000010102" ghsbh="000001"/>
<ecsjsjhtxx sjbh="0000020101" ghsbh="000002"/>
<ecsjsjhtxx sjbh="0000030101" ghsbh="000003"/>
xie_yanke 2009-07-25
  • 打赏
  • 举报
回复
...Replace("/>", @"\>\n");
xie_yanke 2009-07-25
  • 打赏
  • 举报
回复
一样哈,有没有回车有啥关系?对于xml来说,有没有回车对数据是没关系的。如果你非要加回车,
你在得到字符串结果后,...Replace("/>", "\>\n");
emailqjc 2009-07-25
  • 打赏
  • 举报
回复
还是不对,这样后格式如下:
<ecsjsjhtxx sjbh="0000010101" ghsbh="000001"/><ecsjsjhtxx sjbh="0000010102" ghsbh="000001"/><ecsjsjhtxx sjbh="0000020101" ghsbh="000002"/><ecsjsjhtxx sjbh="0000030101" ghsbh="000003"/><ecsjsjhtxx sjbh="0000040101" ghsbh="000004"/><ecsjsjhtxx sjbh="0000050101" ghsbh="000005"/><ecsjsjhtxx sjbh="0000060101" ghsbh="000006"/><ecsjsjhtxx sjbh="0000070101"
而我要的格式如下:
<ecsjsjhtxx sjbh="0000010101" ghsbh="000001"/>
<ecsjsjhtxx sjbh="0000010102" ghsbh="000001"/>
<ecsjsjhtxx sjbh="0000020101" ghsbh="000002"/>
<ecsjsjhtxx sjbh="0000030101" ghsbh="000003"/>
xie_yanke 2009-07-25
  • 打赏
  • 举报
回复
select * from [表名] for xml RAW('ecsjsjhtxx')
mbh0210 2009-07-25
  • 打赏
  • 举报
回复
DataSet就ok了,
emailqjc 2009-07-25
  • 打赏
  • 举报
回复
我就是用的dataset.WriteXML();
写法如下:
String sConnection = "Data Source=127.0.0.1;Initial Catalog=testdb;User ID=sa;PassWord=newman2007";
SqlConnection mySqlConnection = new SqlConnection(sConnection);
mySqlConnection.Open();
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(" select * from ecsjsjhtxx ", sConnection);
DataSet myDataSet2 = new DataSet();
mySqlDataAdapter.Fill(myDataSet2);
myDataSet2.WriteXml("e:\\000000000000000000000000000000.XML");
tangyong12 2009-07-25
  • 打赏
  • 举报
回复
直接用dataset.WriteXML();

111,094

社区成员

发帖
与我相关
我的任务
社区描述
.NET技术 C#
社区管理员
  • C#
  • AIGC Browser
  • by_封爱
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告

让您成为最强悍的C#开发者

试试用AI创作助手写篇文章吧