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
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
Here's a proc we use to generate xml files:
ALTER PROCEDURE [attributionCB].SP_GET_XML_OBJ
WITH
EXECUTE AS CALLER
AS
BEGIN
BEGIN TRY
END TRY
BEGIN CATCH
', @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.