Search code examples
sql-serverxmlstored-proceduressqlcmd

Issue when using SQLCMD to save XML from stored procedure

I am running a stored procedure which generates a single XML column which I want to save as an XML file from sqlcmd.

The problem that I am having is that the XML is not formatted properly for example if the XML element has a lot of information in it it will cut off before all of the information has gone into the element.

Another problem is that the whole XML file itself is no where near long enough. I have about 26000 rows that need to be put into the XML file and there is only about 600 being put into it with what I am running.

This is the stored procedure that I am running:

CREATE PROCEDURE usp_MyProc
AS
   ;WITH XMLNAMESPACES( 'Products' AS p )

        SELECT 
            ProductID
            , ProductName
            , Photo 
            , Price
            , Attribute1 AS [p:attribute/@name]
            , ISNULL( p.Attribute1Value, '' ) AS [p:attribute]
            , ''
            , Attribute2 AS [p:attribute/@name]
            , ISNULL( Attribute2Value, '' ) AS [p:attribute]
            , ''
            , Attribute3 AS [p:attribute/@name]
            , ISNULL( Attribute3Value, '' ) AS [p:attribute]
            , ''
            , Attribute4 AS [p:attribute/@name]
            , ISNULL( Attribute4Value, '' ) AS [p:attribute]
            , ''
        FROM MyDatabase.dbo.MyTable
        FOR XML PATH( 'Item' ), ROOT( 'Products' )

And this is the SQLCMD that I am running from command prompt:

sqlcmd -S "MyServer" -d "MyDatabase" -E -Q "EXEC usp_MyProc" -o "C:\temp\Test.xml" -W

I have read in a few places that :XML ON will sort this out but I have put that in both the stored procedure and the SQLCMD and it does not recognise it.

Any help is appreciated.

Solution

  • I have managed to figure this out. I used an input file with the query to generate the XML and then save the output file as an XML document.

    The input file starts with :XML ON USE [MyDatabase] and then the query to return the XML.

    In SSMS this shows as incorrect syntax but when it runs through command prompt it works fine

    sqlcmd -S "ServerName" -d "DatabaseName" -i "C:\Temp\xmltest.sql" -o "C:\temp\results.xml"