select '<?xml version = "1.0" encoding= "GBK"?> <trans_list> <trans> <main> <s_ent_name' + cast(销售企业 as nvarchar(20)) + '</s_ent_name> <pass_num>' + cast(批准文号 as nvarchar(20)) + '...(后面省略)' from table_name where ...
不错,但这里似乎是一个字段一个节点,所以要加上 ELEMENTS 吧。 select .... from table_name FOR XML AUTO , ELEMENTS
Select * From sfunctions for xml auto ,elements && Learning...
不行啊,运行结果: “无法将未命名的列或表名用作 XML 标识符。请在 SELECT 语句中用 AS 来命名未命名的列。”
select ' <?xml version = "1.0" encoding= "GBK"?> <s_ent_name' + cast(销售企业 as nvarchar(20)) + ' </s_ent_name> <pass_num>' + cast(批准文号 as nvarchar(20)) + '</pass_num>' from table_name for xml auto,elements
select ' <?xml version = "1.0" encoding= "GBK"?> <s_ent_name' + cast(销售企业 as nvarchar(20)) + ' </s_ent_name> <pass_num>' + cast(批准文号 as nvarchar(20)) + ' </pass_num>' from table_name这个就行了,不要加for xml auto,elements
select ' <?xml version = "1.0" encoding= "GBK"?> <s_ent_name' + cast(销售企业 as nvarchar(20)) + ' </s_ent_name> <pass_num>' + cast(批准文号 as nvarchar(20)) + ' </pass_num>' from table_name 后边不要嘉for xml auto,elements 你本来就是在拼xml的,就不用for xml 了哈
不加 for xml auto,elements 运行结果是这样的; <?xml version = "1.0" encoding= "GBK"?> <s_ent_name去痛片 </s_ent_name> <pass_num>上海信谊 </pass_num> <?xml version = "1.0" encoding= "GBK"?> <s_ent_name酚氨咖敏片 </s_ent_name> <pass_num>上海华氏 </pass_num> <?xml version = "1.0" encoding= "GBK"?> <s_ent_name维生素c片 </s_ent_name> <pass_num>上海辛帕斯 </pass_num>加了for xml auto,elements 结果是这样的<jinshan> <?xml version = "1.0" encoding= "GBK"?> <s_ent_name去痛片 </s_ent_name> <pass_num>上海信谊 </pass_num></jinshan> <jinshan> <?xml version = "1.0" encoding= "GBK"?> <s_ent_name酚氨咖敏片 </s_ent_name> <pass_num>上海华氏 </pass_num></jinshan> <jinshan> <?xml version = "1.0" encoding= "GBK"?> <s_ent_name维生素c片 </s_ent_name> <pass_num>上海辛帕斯 </pass_num></jinshan> 好像都不大好啊
能不能用SQL Server 2000的“导入和导出数据”工具来生成XML文件
<?xml version = "1.0" encoding= "GBK"?> <s_ent_name维生素c片 </s_ent_name> <pass_num>上海辛帕斯 </pass_num>这个...后面缺的部分自己补上不就成了,给你个完整版的 select '<?xml version="1.0" encoding="GBK"?> <trans_list> <trans> <main> <s_ent_name>' + cast(销售企业 as nvarchar(40)) + '</s_ent_name> <pass_num>' + cast(批准文号 as nvarchar(40)) + '</pass_num> <drug_name>' + cast(药品通用名 as nvarchar(40)) + '</drug_name> <goods_manu>' + cast(生产厂家 as nvarchar(40)) + '</goods_manu> <standard>' + cast(规格 as nvarchar(40)) + '</standard> <lot_num>' + cast(批号 as nvarchar(40)) + '</lot_num> </main> </trans> </trans_list>' from table_name
'<?xml version = "1.0" encoding= "GBK"?>
<trans_list>
<trans>
<main>
<s_ent_name' + cast(销售企业 as nvarchar(20)) + '</s_ent_name>
<pass_num>' + cast(批准文号 as nvarchar(20)) + '...(后面省略)'
from table_name
where ...
select ....
from table_name
FOR XML AUTO
, ELEMENTS
Select * From sfunctions
for xml auto
,elements
&& Learning...
“无法将未命名的列或表名用作 XML 标识符。请在 SELECT 语句中用 AS 来命名未命名的列。”
' <?xml version = "1.0" encoding= "GBK"?>
<s_ent_name' + cast(销售企业 as nvarchar(20)) + ' </s_ent_name>
<pass_num>' + cast(批准文号 as nvarchar(20)) + '</pass_num>'
from table_name
for xml auto,elements
' <?xml version = "1.0" encoding= "GBK"?>
<s_ent_name' + cast(销售企业 as nvarchar(20)) + ' </s_ent_name>
<pass_num>' + cast(批准文号 as nvarchar(20)) + ' </pass_num>'
from table_name这个就行了,不要加for xml auto,elements
select
' <?xml version = "1.0" encoding= "GBK"?>
<s_ent_name' + cast(销售企业 as nvarchar(20)) + ' </s_ent_name>
<pass_num>' + cast(批准文号 as nvarchar(20)) + ' </pass_num>'
from table_name 后边不要嘉for xml auto,elements
你本来就是在拼xml的,就不用for xml 了哈
<?xml version = "1.0" encoding= "GBK"?> <s_ent_name去痛片 </s_ent_name> <pass_num>上海信谊 </pass_num>
<?xml version = "1.0" encoding= "GBK"?> <s_ent_name酚氨咖敏片 </s_ent_name> <pass_num>上海华氏 </pass_num>
<?xml version = "1.0" encoding= "GBK"?> <s_ent_name维生素c片 </s_ent_name> <pass_num>上海辛帕斯 </pass_num>加了for xml auto,elements 结果是这样的<jinshan> <?xml version = "1.0" encoding= "GBK"?>
<s_ent_name去痛片 </s_ent_name>
<pass_num>上海信谊 </pass_num></jinshan>
<jinshan> <?xml version = "1.0" encoding= "GBK"?>
<s_ent_name酚氨咖敏片 </s_ent_name>
<pass_num>上海华氏 </pass_num></jinshan>
<jinshan> <?xml version = "1.0" encoding= "GBK"?>
<s_ent_name维生素c片 </s_ent_name>
<pass_num>上海辛帕斯 </pass_num></jinshan>
好像都不大好啊
select
'<?xml version="1.0" encoding="GBK"?>
<trans_list>
<trans>
<main>
<s_ent_name>' + cast(销售企业 as nvarchar(40)) + '</s_ent_name>
<pass_num>' + cast(批准文号 as nvarchar(40)) + '</pass_num>
<drug_name>' + cast(药品通用名 as nvarchar(40)) + '</drug_name>
<goods_manu>' + cast(生产厂家 as nvarchar(40)) + '</goods_manu>
<standard>' + cast(规格 as nvarchar(40)) + '</standard>
<lot_num>' + cast(批号 as nvarchar(40)) + '</lot_num>
</main>
</trans>
</trans_list>' from table_name
</s_ent_name> <pass_num>上海信谊 </pass_num>
<s_ent_name酚氨咖敏片 </s_ent_name> <pass_num>上海华氏 </pass_num>
<s_ent_name维生素c片 </s_ent_name> <pass_num>上海辛帕斯 </pass_num>如果这个可以满足你的要求,变一下就可以了
for xml auto,elements
<?xml version="1.0" encoding="GBK"?>
<trans_list>
<trans>
<main>
<s_ent_name>销售企业(必填) </s_ent_name>
<pass_num>批准文号(必填) </pass_num>
<drug_name>药品通用名(必填) </drug_name>
<goods_manu>生产厂家(必填) </goods_manu>
<standard>规格(必填) </standard>
<lot_num>批号(必填) </lot_num>
</main>
</trans>
</trans_list>
的东东
<?xml version="1.0" encoding="GBK"?>
<trans_list>
<trans>
<main>
<drug_name>去痛片 </drug_name>
<goods_manu>上海信谊 </goods_manu>
</main>
</trans>
</trans_list>
<?xml version="1.0" encoding="GBK"?>
<trans_list>
<trans>
<main>
<drug_name>酚氨咖敏片 </drug_name>
<goods_manu>上海华氏 </goods_manu>
</main>
</trans>
</trans_list>
。
。
我要的运行结果是:
<?xml version="1.0" encoding="GBK"?>
<trans_list>
<trans>
<main>
<drug_name>去痛片 </drug_name> <goods_manu>上海信谊 </goods_manu>
<drug_name>酚氨咖敏片 </drug_name> <goods_manu>上海华氏 </goods_manu>
</main>
</trans>
</trans_list>