本文介绍了使用SQL Server输出编码为UTF-8的XML文件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个查询,该查询生成XML文件并将其通过<?xml version="1.0"?>
加载到FTP.
I have a query that generates XML files and loads them to FTP with <?xml version="1.0"?>
.
我需要将编码转换为UTF-8,如下所示:
I need to switch encoding to UTF-8 as follows:
<?xml version="1.0" encoding="utf-8"?>
我可以在文本编辑器中手动进行操作.但是不能在SQL Server中做到这一点.
I can do it manually in the text editor. But cannot do it in SQL Server.
我也阅读了这篇文章,但这并没有为解决该问题做出贡献.
I also read this article but that did not contribute in finding solution for the issue.
https://docs.microsoft.com/zh-CN/sql/relational-databases/xml/create-instances-of-xml-data
我的代码:
USE [Audit_DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- to call the procedure use the code below but assign your own path:
-- EXEC [Audit_DBA].[dbo].[StateAndLocalAward_ToXML] 'C:\NLP\Test\NewFolder'
CREATE PROCEDURE [dbo].[StateAndLocalAward_ToXML_VC]
@FileDestinationDir VARCHAR(2000)
AS
DECLARE @FilePath VARCHAR(4000)
DECLARE @SQLStr VARCHAR(8000),
@Cmd VARCHAR(1000),
@Ret INT
IF EXISTS (SELECT * FROM InputTemp.SYS.TABLES WHERE NAME = 'StateAndLocalAward_output')
DROP TABLE InputTemp.dbo.StateAndLocalAward_output;
;WITH CTE_StateAndLocalAward_output AS
(
SELECT TOP 200
sc.stateContractId,
ca.OnviaId AS [Reference], --AS OnviaID,
ca.contractTitle AS [Title],
nigp.NIGPCodeTx AS [NIPGCode],
nigp.NIGPDescriptionLongTx AS [NIPGDescription],
ca.Description,
vm.parentName AS [VendorName],
offering.offeringTx AS [PrimaryOffering],
ca.StartDate,
ca.AwardDt
FROM
ISCore.dbo.StateContract sc WITH (NOLOCK)
INNER JOIN
ISCore.dbo.ContractAward ca WITH (NOLOCK) ON sc.contractAwardId = ca.contractAwardID
INNER JOIN
ISLibrary.dbo.VendorMaster vm WITH (NOLOCK) ON ca.vendorId = vm.vendorID
LEFT OUTER JOIN
ISCore.dbo.CompanyProfile cp WITH (NOLOCK) ON ca.vendorId = cp.vendorId
LEFT OUTER JOIN
ISCore.dbo.Offering offering WITH (NOLOCK) ON ca.offeringID = offering.offeringID
LEFT OUTER JOIN
ISCore.dbo.NIGPSrvc nigp WITH (NOLOCK) ON ca.NIGPCode = nigp.NIGPCodeTx
WHERE
vm.showUnverified = 1 AND sc.stateContractId = -- 464482 stateContractId
AND StartDate >= DATEADD(month, -12, GETDATE())
)
SELECT
*
INTO
InputTemp.dbo.StateAndLocalAward_output
FROM
CTE_StateAndLocalAward_output;
--select * from InputTemp.dbo.StateAndLocalAward_output
DECLARE @StateContractId INT;
--DECLARE @xml XML;
DECLARE Cur_StateAndLocalAward_StateContractId CURSOR FOR
SELECT StateContractId
FROM inputtemp.dbo.StateAndLocalAward_output t1 WITH (NOLOCK)
OPEN Cur_StateAndLocalAward_StateContractId;
FETCH NEXT FROM Cur_StateAndLocalAward_StateContractId INTO @StateContractId -- @xml;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT
@SQLStr = 'SELECT Body.[Reference], Body.[Title], Body.[NIPGCode], Body.[NIPGDescription], Body.[Description], Body.[VendorName], Body.[PrimaryOffering] FROM InputTemp.dbo.StateAndLocalAward_output AS Body where StateContractId = ''' + str(@StateContractId) + ''' FOR XML AUTO, ELEMENTS;'
--SELECT N'<?xml version="1.0" encoding="UTF-8"?>'
SELECT
CAST((SELECT N'<?xml version="1.0" encoding="UTF-8"?>' + (@SQLSTr)) as varchar(8000) /*as XML*/) as SQLStr
-- select CAST((SELECT N'<?xml version="1.0" encoding="UTF-8"?>' +
-- (@SQLSTr)) as XML) as SQLStr
如果我运行以下代码而不是上面的代码,但是将其强制转换为XML type
,则会收到错误消息:
If I run this code instead of the same above but cast as XML type
, I get an error:
select @FilePath = @FileDestinationDir+'\NewFolder'+ltrim(rtrim(str(@StateContractId)))+'.xml'
select @Cmd = ' bcp " ' + @SQLStr + '" queryout '+@FilePath+' -w -r "" -T -S ' +@@ServerName
exec @Ret = master.dbo.xp_cmdshell @Cmd
FETCH NEXT FROM Cur_StateAndLocalAward_StateContractId INTO @StateContractId -- @xml;
END
CLOSE Cur_StateAndLocalAward_StateContractId;
DEALLOCATE Cur_StateAndLocalAward_StateContractId;
GO
推荐答案
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MyXMLTest]
@FileDestinationDir VARCHAR(2000)
-- to call procedure specify your own file path
-- EXEC [Audit_DBA].[dbo].[MyXMLTest] 'E:\NLP\GovwinIQ_Ontology\NewFolder'
AS
SET QUOTED_IDENTIFIER ON
IF OBJECT_ID (N'InputTemp.dbo.XMLTest', N'U') IS NOT NULL
DROP TABLE InputTemp.dbo.XMLTest;
CREATE TABLE InputTemp.dbo.XMLTest
(
[Id] INT NOT NULL,
[FirstName] VARCHAR(100) NOT NULL,
[LastName] VARCHAR(100) NOT NULL,
[Address] VARCHAR(100) NOT NULL
);
INSERT INTO InputTemp.dbo.XMLTest ([Id], [FirstName], [LastName], [Address])
VALUES (12, 'Zhuk', 'Termik', '123 Gam Str, Boston, NY');
--SELECT * FROM InputTemp.dbo.XMLTest
DECLARE @FilePath VARCHAR(4000)
DECLARE @SQLStr NVARCHAR(4000),
@Cmd NVARCHAR(4000),
@Ret INT
DECLARE @Id INT;
SELECT @Id = 12;
SELECT @SQLStr =
'SELECT N''<?xml version=''''1.0'''' encoding=''''UTF-8''''?>'' + (SELECT CAST((SELECT [Id], [FirstName], [LastName], [Address] FROM InputTemp.dbo.XMLTest AS Body WHERE Id = ''' + str(@Id) + ''' FOR XML AUTO, ELEMENTS) AS NVARCHAR(MAX)))'
SELECT @SQLStr AS SQLStr
SELECT @FilePath = @FileDestinationDir+'\NewFolder'+ltrim(rtrim(str(@Id)))+'.xml'
SELECT @Cmd = ' bcp " ' + @SQLStr + '" queryout '+@FilePath+' -c -C65001 -r "" -T -S ' +@@ServerName
EXEC @Ret = master.dbo.xp_cmdshell @Cmd
IF OBJECT_ID (N'InputTemp.dbo.XMLTest', N'U') IS NOT NULL
DROP TABLE InputTemp.dbo.XMLTest;
GO
这篇关于使用SQL Server输出编码为UTF-8的XML文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!