I need to create XML files based on data in a base table using SQL Server BCP, however the XML I'm using contains hyperlinks and I think SSMS is interpreting these hyperlinks differently to normal text, so they are not encapsulated correctly within single quotes. Here is the code:
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)
The error I'm getting is
Incorrect syntax near '/'.
With everything being in one line it makes troubleshooting a bit difficult, but the only places I'm using / is in the hyperlinks and the hierarchy structure for the XML layout.
Here's the XML script outside of 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
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')
Here is the sample data for the base table I'm using:
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)
Is it the hyperlinks that's causing the error message or is it the XML hierarchy (\[header/env:envelope/env:source/@password\]).
I've added SET QUOTED_IDENTIFIER OFF to the script to hopefully instruct SQL to use "" and [] as identifiers but to no avail.
