Generating XML files from SQL server with bcp gives errors

Question

I'm trying to generate xml files based on a base table using bcp. Here is the script:

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

I'm facing these erorrs:

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],'.

Error = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Missing end comment mark '*/'.

Error = [Microsoft][ODBC Driver 17 for SQL Server]Unable to resolve column level collations

The first error points to a static value I'm passing (''ORDERRESPONSE'' in this case) which I don't understand. It seems correctly encapsulated. The script has a problem with all static values I'm passing though, so changing just the one doesn't help.

The second error is the missing end comment mark. I received assistance from @AlwaysLearning yesterday who gave me pointers to use in the XML script. This piece worked well displaying the results but when trying to use this within bcp fails. Obviously I can't add an end comment as this isn't supposed to be used as a comment. It is as if bcp ignores the encapsulation and sees the start comment in the context outside where it is used.

The third error message I'm unsure about.

I've searched for solutions to all these problems to no avail. I realize it might not be a very pretty script however switching over to Powershell now isn't a solution really as our company doesn't have someone with PS knowledge.

To generate the base table:

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
)

Populate base table:

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)

Isn't this the same as your last question? [How to generate XML files from SQL Server with hyperlinks?](https://stackoverflow.com/questions/75600704/how-to-generate-xml-files-from-sql-server-with-hyperlinks) — Thom A, Mar 01 '23 at 11:02
As a note, however (and many others), dislike the idea of using SQL Server, to call command shell, to call BCP, to connect to the SQL Server that all this started from; it's a clunky and flaky method. Use actual middleware intended for ETL. — Thom A, Mar 01 '23 at 11:03
@Larnu no my previous question around using hyperlinks was solved by adding hte xp_cmdshell. The process is now actually trying to generate the xml files (its generating them blank) but these errors crept up now. — Omen, Mar 01 '23 at 11:06
@Larnu what would the correct use of tools be to extract data from a SQL db and create XML files from it? — Omen, Mar 01 '23 at 11:07
Well the massive commandline query you're trying to feed to `bcp` contains `"` characters that you're not escaping. bcp is great for native format server-to-server data transfers, but it's really awful for just about every other use case. If you're determined to use `bcp` you'd probably have a much easier time of it if you moved all of that T-SQL code into a stored procedure or user-defined function and just invoked that using bcp's `queryout`. — AlwaysLearning, Mar 01 '23 at 11:42
The lines that start `''` should be `''''`. And `[TBCO_Staging}` should be `TBCO_Staging` I'm not sure what the backslashes are trying to achieve. Consider using `Invoke-SqlCmd` in Powershell. You can kick this off with an Agent job if necessary. — Charlieface, Mar 01 '23 at 11:58
The `.query('/*')` seems unnecessary. Also might be wiser to change the `default` namespace rather than trying to mangle the inner query with a new default. — Charlieface, Mar 01 '23 at 12:12
Thank you to all the suggestions. I think I'm at the stage where I realize I can't generate this with SQL to XML files. How would Powershell handle the creation of all the XML tags, or should I save the XML data in the database first and then export that using Powershell? Instead of selecting the data from columns and using my script to encapsulate everything in XML THEN export as XML files, should I store the data as XML in SQL then export? My concern with this is the data needs to be split based on location/supplier, so there are going to be multiple xml files (~400), not just one big one. — Omen, Mar 01 '23 at 12:43
The best way is to package your XML generating T-SQL as a stored procedure. It will allow to eliminate complicated string manipulations. Test it first on its own. And after that the bcp call will be very easy to make. — Yitzhak Khabinsky, Mar 01 '23 at 13:40
As i wrote to you in the other question, don't use the whole XML-shebang with bcp, create a table where you store your xml and then just use select * from yourtable in bcp call — siggemannen, Mar 01 '23 at 14:46
I've made the change to generate tables with XML data populated and then generate files based on the tables. Looks much cleaner. Thanks to all the assistance and comments — Omen, Mar 02 '23 at 06:14

Generating XML files from SQL server with bcp gives errors

0 Answers0