Issue when using SQLCMD to save XML from stored procedure

Question

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.

Have no time to test this at the moment, but in general: 1) Pure reading should not be done with a SP, try to create a scalar function returning XML and call it with SELECT dbo.MyFunction(). 2) you are using a namespace "p:" I doubt that this would work without WITH XMLNAMESPACES. 3) It might help to set a ,TYPE behind the ,ROOT() — Gottfried Lesigang
– Gottfried Lesigang, Commented Apr 29, 2016 at 8:21
I am using WITH XMLNAMESPACES, I just forgot to copy it into this, I will edit and put it in. I will also try your recommendation. Thanks — Cornflake2068
– Cornflake2068, Commented Apr 29, 2016 at 8:24

Cornflake2068 · Accepted Answer · 2016-04-29 12:01:57Z

1

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"

answered Apr 29, 2016 at 12:01

Cornflake2068

5331 gold badge6 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Issue when using SQLCMD to save XML from stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related