Generating XML from MSSQL for the XML Import Utility

The XML Import Utility is a powerful tool for importing large volumes of data into the software. To easily generate XML from Microsoft SQL, the "FOR XML" command can be appended to a select statement to provide data for direct import.

Example SQL statement:

SELECT

'10' AS EV870_ORG_CODE,

LastName + ', ' + FirstName AS EV870_NAME,

FirstName AS EV870_FIRST_NAME,

LastName AS EV870_LAST_NAME,

Title AS EV870_TITLE

FROM Northwind.dbo.Employees AS EV870_ACCT_MASTER

WHERE City = 'Redmond'

FOR XML AUTO, ROOT('ROOT'),ELEMENTS

Produces

<ROOT>

<EV870_ACCT_MASTER>

<EV870_ORG_CODE>10</EV870_ORG_CODE>

<EV870_NAME>Peacock, Margaret</EV870_NAME>

<EV870_FIRST_NAME>Margaret</EV870_FIRST_NAME>

<EV870_LAST_NAME>Peacock</EV870_LAST_NAME>

<EV870_TITLE>Sales Representative</EV870_TITLE>

</EV870_ACCT_MASTER>

</ROOT>

Comments

2 comments

Sean Tame
- November 10, 2013 22:49
WOW, I can see this coming in handy very soon.

Any hints on commands that can save the output file to a location?

0 upvotes

Cedric Strauss - Palexpo - Geneva

May 08, 2015 11:58

Here's a proc we use to generate xml files:

ALTER PROCEDURE [attributionCB].SP_GET_XML_OBJ
WITH
EXECUTE AS CALLER
AS
BEGIN

DECLARE @vNomProc VARCHAR(200)
SET @vNomProc = 'attributionCB.SP_GET_XML_OBJ'

BEGIN TRY

DECLARE @SQLCmd VARCHAR(8000)

SELECT @pvNomFichierXml = REPLACE(@pvNomFichierXml,'%item_id%',ISNULL(CAST(@pv_item_id AS VARCHAR(MAX)), ''))

SELECT  @SQLCmd = 
'bcp "' +  
'SELECT ' +
'(SELECT ' +
'      er100_org_code, ' +
'      er100_evt_id, ' +
'      er100_ord_type, ' +
'      er100_func_id, ' +
'      er100_price_list, ' +
'      er100_res_phase, ' +
'      er100_ord_acct, ' +
'      er100_bill_to_cust, ' +
'      er100_req_cust, ' +
'      er100_pl_currency, ' +
'      er100_user_6x, ' +
'      er100_assignment_name, ' +
'      er100_new_sts, ' +
'      er100_ng_ord_contact, ' +
'      er100_ng_bto_contact, ' +
'      er100_ng_req_contact, ' +
'      er100_alt_assign_name_1, ' +
'      er100_alt_assign_name_2, ' +
'      ( ' +
'        SELECT ' +
'          er101_phase, ' +
'          er101_res_qty, ' +
'          CONVERT(VARCHAR,er101_start_date_iso,120) er101_start_date_iso, ' +
'          CONVERT(VARCHAR,er101_end_date_iso,120) er101_end_date_iso, ' +
'          CONVERT(VARCHAR,er101_start_time_iso,120) er101_start_time_iso, ' +
'          CONVERT(VARCHAR,er101_end_time_iso,120) er101_end_time_iso, ' +
'          er101_pr_list_dtl, ' +
'          er101_price_list, ' +
'          er101_pl_unit_chrg, ' +
'          er101_order_form, ' +
'          er101_user_nbr_060p, ' +
'          ''XMLREF : '' + cast([ER101_ITEM_ID] as varchar) + ''-'' + cast([ER101_ITEM_DTL_ID]as varchar) as er101_ref_field ' +
'        FROM '+db_name()+'.attributionCB.t_er101_acct_order_dtl ' +
'        WHERE t_er101_acct_order_dtl.er101_item_id = t_er100_acct_order.er100_item_id ' +
'       FOR XML PATH(''ER101_ACCT_ORDER_DTL''),TYPE)  ' +
'    FROM '+db_name()+'.attributionCB.T_ER100_ACCT_ORDER ' +
'    WHERE ' +
'      er100_org_code = ''' + @p_societe +''' AND er100_evt_id = ' + cast(@p_evenement as varchar)+ ' AND er100_item_id = ' + cast(@pv_item_id as varchar)+ 
'   FOR ' +
'   XML PATH(''ER100_ACCT_ORDER''),TYPE) ' +
'FOR XML PATH(''ROOT'') ' + 
'"' +
+ ' queryout '  +  @pvNomFichierXml +  ' -w -r -t -T -S ' + @@SERVERNAME  

EXECUTE master..xp_cmdshell @SQLCmd

END TRY
BEGIN CATCH

DECLARE 
    @vErrorProc VARCHAR(200),
    @iErrorLine INT,
    @iErrorSeverity INT,
    @iErrorState INT,
    @vErrorMessage VARCHAR(4000),
    @vErrorParams VARCHAR(8000)

SELECT 
    @vErrorProc = ISNULL(ERROR_PROCEDURE(), '')
    , @iErrorLine  = ERROR_LINE()
    , @iErrorSeverity = ERROR_SEVERITY()
    , @iErrorState  = ERROR_STATE()
    , @vErrorMessage = ERROR_MESSAGE()
    , @vErrorParams = 'EXEC ' + ISNULL(@vNomProc, 'NULL')

' @p_societe = ' + ISNULL('''' + @p_societe + '''', 'NULL')
', @p_evenement = ' + ISNULL(CAST(@p_evenement AS VARCHAR(10)), 'NULL')
', @pv_item_id = ' + ISNULL(CAST(@pv_item_id AS VARCHAR(10)), 'NULL')
', @pvNomFichierXml = ' + ISNULL('''' + @pvNomFichierXml + '''', 'NULL')

SELECT @vErrorMessage = SUBSTRING(ISNULL(@vErrorMessage, 'NULL') + '
@@NESTLEVEL : ' + CAST(@@NESTLEVEL AS varchar(10)) + '
' + @vErrorParams + '
@vErrorProc = ' + ISNULL(@vErrorProc, 'NULL')
', @iErrorLine = ' + ISNULL(CAST(@iErrorLine AS varchar(10)), 'NULL') + '
**********'
, 1, 8000)

RAISERROR(@vErrorMessage, @iErrorSeverity, @iErrorState)

END CATCH
END

0 upvotes

Please sign in to leave a comment.

Related articles