SQL附加<?xml version ="1.0" encoding ="UTF-8"?查询输出 [英] SQL appending <?xml version="1.0" encoding="UTF-8"?> to Query output

问题描述

所以我在弄清楚如何进行这项工作时遇到了麻烦 这样我可以附加xml编码 对我的查询.

So i'm having a bit of trouble to figure out how i could make this work so that i can append the xml encoding to my query.

这是我到目前为止共同努力的目标:

this is what i hacked together so far:

DECLARE @FileName VARCHAR(50)
DECLARE @SQLCmd   VARCHAR(8000)

SELECT  @FileName = 'C:\SampleXMLOutput.xml'

-- in this command, we are making sure there is only one ROOT node


SELECT @SQLCmd = + 'bcp ' + 
                         '"SELECT Id, Initials, firstname, lastname, email ' +
                         ' FROM Employees.dbo.IDCards ' +
                         ' FOR XML PATH(''Employee''), ELEMENTS,  ROOT(''Employees''), TYPE "' +
                         ' queryout '  +
                   @FileName +
                   ' -w -T -S' + @@SERVERNAME



-- display command, for visual  check
SELECT @SQLCmd AS 'Command to execute'

-- create the XML file
EXECUTE master..xp_cmdshell @SQLCmd

我还需要从同一查询输出中的另一个表中获取一些数据.

Also i need to get some data from another table within the same query output.

任何帮助表示赞赏

推荐答案

由于语法的原因，我假设您正在使用SQL Server ...

I assume - due to the syntax - that you are using SQL Server...

使用FOR XML PATH可以创建这样的processing instruction

SELECT 'test' AS OneNormalElement
      ,'version="1.0" encoding="UTF-8"' AS [processing-instruction(abc)]
FOR XML PATH('Test')

结果

<Test>
  <OneNormalElement>test</OneNormalElement>
  <?abc version="1.0" encoding="UTF-8"?>
</Test>

但是您不允许使用AS [processing-instruction(xml)].您收到错误:

But you are not allowed to use AS [processing-instruction(xml)]. You get the error:

错误:6879，严重性:16，"xml"是无效的XML处理 教学目标.可能构造XML声明的尝试 使用XML处理指令的构造函数. XML声明 不支持使用FOR XML进行构建.

Error: 6879, Severity: 16, ‘xml’ is an invalid XML processing instruction target. Possible attempt to construct XML declaration using XML processing instruction constructor. XML declaration construction with FOR XML is not supported.

实际上不支持在XML之外创建任何PI ...

我链接为重复"的问题显示了一些解决方法，无论如何都要添加...

The question I've linked as "duplicate" shows some workarounds how to add this anyhow...

对于迟到的答复以及错误的提示，我必须深表歉意. xml声明的处理方式不同，因此链接的答案实际上并没有帮助.我也更新了这个...

I must apologize for the late response as well as for the wrong hint. The xml-declaration is handled differently, so the linked answer did not help actually. I updated this also...

我发现添加XML声明的唯一方法是string concatenation:

The only way I found to add an XML-declaration is string concatenation:

DECLARE @ExistingXML XML=
(
    SELECT 
        'Test' AS Test,
        'SomeMore' AS SomeMore
    FOR XML PATH('TestPath'),TYPE
);

DECLARE @XmlWithDeclaration NVARCHAR(MAX)=
(
    SELECT '<?xml version="1.0" encoding="UTF-8"?>'
           +
           CAST(@ExistingXml AS NVARCHAR(MAX))
);
SELECT @XmlWithDeclaration;

如果要在一个呼叫中完成此操作，则必须在没有,TPYE

And if you want to do it in one single call, you must do this without ,TPYE

DECLARE @XmlWithDeclaration NVARCHAR(MAX)=
(
    SELECT '<?xml version="1.0" encoding="UTF-8"?>'
           +
            (
            SELECT 
                'Test' AS Test,
                'SomeMore' AS SomeMore
            FOR XML PATH('TestPath')
            )
);
SELECT @XmlWithDeclaration;

所以-终于-我希望这是您的解决方案:

So - finally - I hope this is your solution:

SELECT @SQLCmd = + 'bcp ' + 
                     '"SELECT ''<?xml version=""1.0"" encoding=""UTF-8""?>'' + ' + 
                     ' (SELECT Id, Initials, firstname, lastname, email ' +
                     '  FROM Employees.dbo.IDCards ' +
                     '  FOR XML PATH(''Employee''), ELEMENTS,  ROOT(''Employees'')) "' +
                     ' queryout '  +
               @FileName +
               ' -w -T -S' + @@SERVERNAME

这篇关于SQL附加<?xml version ="1.0" encoding ="UTF-8"?查询输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！