使用bcp从SQL Server生成XML文件时遇多类错误求助

执行脚本 #

SET @Script = '
bcp " ;WITH XMLNAMESPACES (''urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01'' as po,\
''aaa://xml.aaa.net/K809/k8msgEnvelope''                                AS env, \
''aaa://www.w3.org/2001/XMLSchema-instance''                                    as xsi,\
default ''aaa://xml.aaa.net/K809'') \
SELECT ''aaa://xml.aaa.net/K809 k8Order.xsd''   AS [@xsi:schemaLocation],\
''                                                      AS [header/env:envelope/env:source/@password],\
''                                                      AS [header/env:envelope/env:source/@machine],\
A.SUPPLIER                                              AS [header/env:envelope/env:source/@Endpoint],\
A.SourceLocation                                        AS [header/env:envelope/env:source/@Branch],\
''                                                      AS [header/env:envelope/env:destination/@machine],\
A.SUPPLIER                                              AS [header/env:envelope/env:destination/@Endpoint],\
A.SourceLocation                                        AS [header/env:envelope/env:destination/@Branch],\
''ORDERRESPONSE''                                       AS [header/env:envelope/env:payload],\
A.FREEATTR3                                             AS [header/env:envelope/env:cfcompany],\
''ILDLive''                                             AS [header/env:envelope/env:service],\
(select ''ABC 1234567''                                 AS [po:OrderReferences/po:CrossReference],cast(''<Extensions xmlns="aaa://xml.aaa.net/k8msg/k8OrderExtensions"><Direct>FALSE</Direct></Extensions>'' as xml).query(''/*''),\
A.SUPPLIER                                              AS [po:Supplier/po:SupplierReferences/po:BuyersCodeForSupplier],\
A.EXPDATEPRD                                            AS [po:Delivery/po:PreferredDate],\
''Test text for Special instructions''                  AS [po:Delivery/po:SpecialInstructions],\
''New item''                                            AS [po:OrderLine/@TypeDescription],\
''New''                                                 AS [po:OrderLine/@TypeCode],\
''Add''                                                 AS [po:OrderLine/@Action],\
A.ITEM                                                  AS [po:OrderLine/po:Product/po:BuyersProductCode],\
A.ExternalItemMasterID                                  AS [po:OrderLine/po:Product/po:SuppliersProductCode],\
A.UnitOfMeasureDesc                                     AS [po:OrderLine/po:Quantity/@UOMDescription],\
A.VolumetricValue                                       AS [po:OrderLine/po:Quantity/@UOMCode],\
CAST(SUM(A.QEDIT) AS INT)                               AS [po:OrderLine/po:Quantity/po:Amount],\
DATEDIFF(DAY,''1989/12/31'',A.EXPDATEPRD)               AS [po:OrderLine/po:Delivery/po:PreferredDate]\
for xml path(''po:PurchaseOrder''), type).query(''declare default element namespace "urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01";<PurchaseOrder> { /po:PurchaseOrder/* } </PurchaseOrder>'') as [body] \
FROM [TBCO_Staging}.[dbo].[BaseTemp] AS A \
GROUP BY A.Supplier,A.FREEATTR3,A.SUPPLIER,A.EXPDATEPRD,A.ExternalItemMasterID,A.ITEM,A.VolumetricValue,A.UnitOfMeasureDesc, A.SourceLocation \
FOR XML PATH(''kmsg'')" \
queryout "F:\directory\Exports\XMLFiles\test.xml" -S someserver -T -w -r -t -c -C 65001
'
EXEC MASTER..xp_cmdshell @Script

错误信息 #

1. Error = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ' AS [header/env:envelope/env:payload],A.FREEATTR3 AS [header/env:envelope/env:cfcompany],'
2. Error = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Missing end comment mark '*/'
3. Error = [Microsoft][ODBC Driver 17 for SQL Server]Unable to resolve column level collations

问题困惑 #

• 第一个错误指向静态值'ORDERRESPONSE'，但该值已正确封装，且所有静态值均触发此类问题；
• 第二个错误提示缺少注释结束标记，但脚本中并无注释，推测bcp未正确解析封装内容；
• 第三个错误的含义尚不明确。

BaseTemp表结构与测试数据 #

创建表脚本 #

CREATE TABLE [dbo].[BaseTemp](
    [RowNo] [bigint] NULL,
    [Supplier] [varchar](12) NULL,
    [SourceLocation] [varchar](8) NULL,
    [FREEATTR3] [varchar](70) NULL,
    [EXPDATEPRD] [int] NULL,
    [Item] [varchar](32) NOT NULL,
    [ExternalItemMasterID] [varchar](50) NULL,
    [UnitOfMeasureDesc] [varchar](20) NULL,
    [VolumetricValue] [varchar](50) NULL,
    [QEDIT] [int] NULL,
    [EXPDATEPRDAsDate] [int] NULL
)

插入数据脚本 #

INSERT INTO [dbo].[BaseTemp]
           ([RowNo]
           ,[Supplier]
           ,[SourceLocation]
           ,[FREEATTR3]
           ,[EXPDATEPRD]
           ,[Item]
           ,[ExternalItemMasterID]
           ,[UnitOfMeasureDesc]
           ,[VolumetricValue]
           ,[QEDIT]
           ,[EXPDATEPRDAsDate])
     VALUES
           (2
           ,'050025'
           ,'1053'
           ,'01'
           ,'12120'
           ,1153105
           ,'5103'
           ,'Each'
           ,'EA'
           ,1836
           ,20751)

错误解决方法 #

针对错误1：语法错误 #

• 修正静态字符串闭合：脚本中多个静态值（如'' AS [header/env:envelope/env:source/@password]）存在单引号未闭合问题，空字符串需写成''''（嵌套字符串场景），正确写法为'''' AS [header/env:envelope/env:source/@password],；
• 修正表名语法：FROM [TBCO_Staging}.[dbo].[BaseTemp]括号不匹配，改为FROM [TBCO_Staging].[dbo].[BaseTemp]。

针对错误2：缺少注释结束标记 #

• 问题出在cast(''<Extensions ...>'' as xml).query(''/*'')，SQL将/*识别为注释起始符而非XPath表达式，需修改为.query(''*'')（选取所有子节点），或明确指定节点路径避免误解析，比如.query(''/*[local-name()="Extensions"]'')。

针对错误3：无法解析列级排序规则 #

• 移除GROUP BY重复列：A.Supplier和A.SUPPLIER在SQL中视为同一列，删除其中一个；
• 若仍有排序规则冲突，可显式指定列的排序规则，比如A.Supplier COLLATE SQL_Latin1_General_CP1_CI_AS。

修正后的核心脚本片段示例 #

-- 修正静态字符串闭合与表名
SELECT ''aaa://xml.aaa.net/K809 k8Order.xsd''   AS [@xsi:schemaLocation],
''''                                                      AS [header/env:envelope/env:source/@password],
''''                                                      AS [header/env:envelope/env:source/@machine],
A.SUPPLIER                                              AS [header/env:envelope/env:source/@Endpoint],
...
FROM [TBCO_Staging].[dbo].[BaseTemp] AS A 
-- 修正GROUP BY重复列
GROUP BY A.Supplier,A.FREEATTR3,A.EXPDATEPRD,A.ExternalItemMasterID,A.ITEM,A.VolumetricValue,A.UnitOfMeasureDesc, A.SourceLocation 
-- 修正XPath表达式
cast(''<Extensions xmlns="aaa://xml.aaa.net/k8msg/k8OrderExtensions"><Direct>FALSE</Direct></Extensions>'' as xml).query(''*''),

内容的提问来源于stack exchange，提问作者Omen