如何用SQL Server生成带超链接的XML文件？BCP语法报错排查

我需要用SQL Server的BCP工具，基于BaseTemp表的数据生成包含超链接的XML文件，但执行脚本时一直报「Incorrect syntax near '/'」错误。怀疑是超链接未被正确用单引号包裹，或是XML层级结构（如[header/env:envelope/env:source/@password]）导致的问题，已尝试添加SET QUOTED_IDENTIFIER OFF但无效。

执行代码 #

SET @Script = '
bcp " ;WITH XMLNAMESPACES (''urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01'' as po, ''aaaa://xml.aaa.net/K809/k8msgEnvelope'' AS env, ''aaaa://www.w3.org/2001/XMLSchema-instance'' as xsi, default ''aaaa://xml.aaa.net/K809'') SELECT TOP 1 ''aaaa://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="aaaa://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 #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 "Somedirectory\test.xml" -S GenericServerName -T -w -r -t -c -C 65001
'
EXEC (@Script)

独立XML脚本（可正常运行） #

WITH XMLNAMESPACES (
  'urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01' as po,
  'aaaa://xml.aaa.net/K809/k8msgEnvelope' AS env,
  'aaaa://www.w3.org/2001/XMLSchema-instance' as xsi,
  default 'aaaa://xml.aaa.net/K809'
)

SELECT TOP 1
    'aaaa://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="aaaa://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 BaseTemp AS A
WHERE A.Supplier = @Supplier
AND A.SourceLocation = @Location
GROUP BY A.Supplier,A.FREEATTR3,A.SUPPLIER,A.EXPDATEPRD,A.ExternalItemMasterID,A.ITEM,A.VolumetricValue,A.UnitOfMeasureDesc, A.SourceLocation 
FOR XML PATH('kmsg')

表结构 #

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. 引号转义错误：动态SQL嵌套BCP命令时，单引号转义规则为：字符串中表示一个单引号需写两个；BCP查询参数内的单引号需再次转义（即四个单引号对应一个实际单引号）。原代码存在未正确闭合的单引号，比如'' AS [header/env:envelope/env:source/@password]应改为'''' AS ...。
2. 多余分号干扰：BCP查询开头的;属于冗余字符，会干扰语法解析。

修正后的执行代码 #

SET @Script = '
bcp "WITH XMLNAMESPACES (''''urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01'''' as po, ''''aaaa://xml.aaa.net/K809/k8msgEnvelope'''' AS env, ''''aaaa://www.w3.org/2001/XMLSchema-instance'''' as xsi, default ''''aaaa://xml.aaa.net/K809'''') SELECT TOP 1 ''''aaaa://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=&quot;aaaa://xml.aaa.net/k8msg/k8OrderExtensions&quot;><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 &quot;urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01&quot;;<PurchaseOrder> { /po:PurchaseOrder/* } </PurchaseOrder>'''') as [body] FROM #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 "Somedirectory\test.xml" -S GenericServerName -T -w -r -t -c -C 65001
'
EXEC (@Script)

更简洁的替代方案：封装视图 #

为避免复杂的引号转义，可先将XML查询逻辑封装为视图，再用BCP导出：

1. 创建视图：

CREATE VIEW vw_ExportXML AS
WITH XMLNAMESPACES (
  'urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01' as po,
  'aaaa://xml.aaa.net/K809/k8msgEnvelope' AS env,
  'aaaa://www.w3.org/2001/XMLSchema-instance' as xsi,
  default 'aaaa://xml.aaa.net/K809'
)
SELECT TOP 1
    'aaaa://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="aaaa://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 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')

2. 用BCP导出视图数据：

SET @Script = 'bcp "SELECT * FROM vw_ExportXML" queryout "Somedirectory\test.xml" -S GenericServerName -T -w -r -t -c -C 65001'
EXEC (@Script)

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