locked
Adding xml prolog to XML file RRS feed

  • Question

  • I am trying to create a XML.

    so far I have succeed in creating one but the only thing missing is the xml prolog.

    The only idea i have to solve this is to create two files, one with the xml data and the other with only the xml prolog.

    By merging both I will and up with one file that start with the xml prolog and continues with the xml data.

    I would appreciate if

    - someone has a different suggestion on how to add the xml prolog to a xml file

    or

    - could help me solve the problem I am having in creating a file with xml prolog

    Use Eos93
    
    DECLARE @dataCollected VARCHAR(1000)
    DECLARE @dirdata VARCHAR(1000)
    DECLARE @dirheader VARCHAR(1000)
    DECLARE @dirxml VARCHAR(1000)
    DECLARE @servername VARCHAR(1000)
    DECLARE @strdata VARCHAR(1000)
    DECLARE @xmlProlog VARCHAR(1000)
    DECLARE @strxmlprolog VARCHAR(1000)
    
    Set @xmlProlog = '<?xml version="1.0" encoding="UTF-8" ?>'
    Set @dataCollected = 'select persid, PersonalNr, LastName, FirstName, TelNr, RoomLastACFK from Eos93.dbo.Person FOR XML PATH(''record''), ROOT(''ROOT'')'
    Set @dirdata = 'C:\test\temp\data.xml'
    Set @dirheader = 'C:\test\temp\header.xml'
    Set @dirxml = 'C:\test\List.xml'
    Set @servername = 'WIN-2CGK\SQLEXPRESS'
    
    Set @strdata = 'bcp "'+@dataCollected+'" queryout '+@dirdata+' -c -T -S '+@servername+''
    Set @strxmlprolog = 'bcp "'+@xmlProlog+'" out '+@dirheader+' -c -T -t'
    
    
    EXEC xp_cmdshell @strdata	
    EXEC xp_cmdshell @strxmlprolog
    
    -----merging the files is not done... YET-------
    --SET @strxml = 'copy *.xml  "'+@dirxml+'"
    --EXEC master..xp_cmdshell @strxml
    
    Executing @strxmlprolog results in the following error:

    A valid table name is required for in, out, or format options.

    Thank you in advance and for reading.

    Friday, April 13, 2018 6:34 PM

Answers

  • @Visakh16

    Thank you for your reply. It was very helpful.

    The result is almost correct, unfortunately the double quotes are being removed from the xml prolog.

    Do you have any suggestion for preserving the double quotes?

    The result looks like this:

    <?xml version=1.0 encoding=UTF-8 ?>

    but it should look like this:

    <?xml version="1.0" encoding="UTF-8" ?>

    Cleaned up the code a little bit and included you suggestion:

    Use Eos93
    
    DECLARE 
    	@dataCollected VARCHAR(max),
    	@dirdata VARCHAR(1000),
    	@servername VARCHAR(1000),
    	@strdata VARCHAR(1000),
    	@xmlProlog VARCHAR(1000)
    		
    Set @xmlProlog = '<?xml version="1.0" encoding="UTF-8" ?>'
    Set @dataCollected = 'select isnull(cast(persid as varchar(11)), '''') as persid, isnull(cast(PersonalNr as varchar(11)), '''') as PersonalNr, isnull(cast(LastName as varchar(11)), '''') as LastName, isnull(cast(FirstName as varchar(11)), '''') as FirstName, isnull(cast(TelNr as varchar(11)), '''') as TelNr, isnull(cast(RoomLastACFK as varchar(11)), '''') as Location from Eos93.dbo.Person	order by PersID asc FOR XML PATH(''record''), ROOT(''ROOT'')'
    Set @dataCollected = 'SELECT ''' + @xmlProlog + ''' + (' + @dataCollected + ')'
    
    Set @dirdata = 'C:\test\data.xml'
    
    Set @servername = 'WIN-2CGK\SQLEXPRESS'
    
    Set @strdata = 'bcp "'+ @dataCollected+'" queryout '+@dirdata+' -c -T -S '+@servername+''
    
    EXEC xp_cmdshell @strdata	
    
    GO

    try like this and see

    Use Eos93
    
    DECLARE 
    	@dataCollected VARCHAR(max),
    	@dirdata VARCHAR(1000),
    	@servername VARCHAR(1000),
    	@strdata VARCHAR(1000),
    	@xmlProlog VARCHAR(1000)
    		
    Set @xmlProlog = '<?xml version=""1.0"" encoding=""UTF-8"" ?>'
    Set @dataCollected = 'select isnull(cast(persid as varchar(11)), '''') as persid, isnull(cast(PersonalNr as varchar(11)), '''') as PersonalNr, isnull(cast(LastName as varchar(11)), '''') as LastName, isnull(cast(FirstName as varchar(11)), '''') as FirstName, isnull(cast(TelNr as varchar(11)), '''') as TelNr, isnull(cast(RoomLastACFK as varchar(11)), '''') as Location from Eos93.dbo.Person	order by PersID asc FOR XML PATH(''record''), ROOT(''ROOT'')'
    Set @dataCollected = 'SELECT ''' + @xmlProlog + ''' + (' + @dataCollected + ')'
    
    Set @dirdata = 'C:\test\data.xml'
    
    Set @servername = 'WIN-2CGK\SQLEXPRESS'
    
    Set @strdata = 'bcp "'+ @dataCollected+'" queryout '+@dirdata+' -c -T -S '+@servername+''
    
    EXEC xp_cmdshell @strdata	
    
    GO


    Then you will get your desired result


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page



    • Edited by Visakh16MVP Saturday, April 14, 2018 8:54 AM
    • Marked as answer by Phyxius7 Saturday, April 14, 2018 8:57 AM
    Saturday, April 14, 2018 8:24 AM

All replies

  • A valid table name is required for in, out, or format options.


    Thank you in advance and for reading.

    Speaking of reading, did you read the error message yourself?

    You have:

    Set @strdata = 'bcp "'+@dataCollected+'" queryout '+@dirdata+' -c -T -S '+@servername+''
    Set @strxmlprolog = 'bcp "'+@xmlProlog+'" out '+@dirheader+' -c -T -t'

    As for what you are trying to achieve, the FOR XML methods in SQL Server does to produce a prologue so that tends to beomce a kludge.

    • Proposed as answer by Visakh16MVP Saturday, April 14, 2018 6:24 AM
    Friday, April 13, 2018 10:07 PM
  • I think your attempt is to add the xml version info in front

    In that case you can use below method

    DECLARE @dataCollected VARCHAR(1000)
    DECLARE @dirdata VARCHAR(1000)
    DECLARE @dirheader VARCHAR(1000)
    DECLARE @dirxml VARCHAR(1000)
    DECLARE @servername VARCHAR(1000)
    DECLARE @strdata VARCHAR(1000)
    DECLARE @xmlProlog VARCHAR(1000)
    DECLARE @strxmlprolog VARCHAR(1000)
    
    Set @xmlProlog = '<?xml version="1.0" encoding="UTF-8" ?>'
    Set @dataCollected = 'select persid, PersonalNr, LastName, FirstName, TelNr, RoomLastACFK from Eos93.dbo.Person FOR XML PATH(''record''), ROOT(''ROOT'')'
    
    Set @dirdata = 'D:\data.xml'
    
    Set @dirheader = 'C:\test\temp\header.xml'
    Set @dirxml = 'C:\test\List.xml'
    Set @dataCollected = 'SELECT ''' + @xmlProlog + ''' + (' + @dataCollected + ')'
    
    Set @servername = 'WIN-2CGK\SQLEXPRESS'
    
    Set @strdata = 'bcp "'+ @dataCollected+'" queryout '+@dirdata+' -c -T -S '+@servername+''
    --Set @strxmlprolog = 'bcp "''" out '+@dirheader+' -c -T -t'
    
    
    EXEC xp_cmdshell @strdata	
    --EXEC xp_cmdshell @strxmlprolog
    
    -----merging the files is not done... YET-------
    --SET @strxml = 'copy *.xml  "'+@dirxml+'"
    --EXEC master..xp_cmdshell @strxml
    
    


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Saturday, April 14, 2018 6:41 AM
  • @Visakh16

    Thank you for your reply. It was very helpful.

    The result is almost correct, unfortunately the double quotes are being removed from the xml prolog.

    Do you have any suggestion for preserving the double quotes?

    The result looks like this:

    <?xml version=1.0 encoding=UTF-8 ?>

    but it should look like this:

    <?xml version="1.0" encoding="UTF-8" ?>

    Cleaned up the code a little bit and included you suggestion:

    Use Eos93
    
    DECLARE 
    	@dataCollected VARCHAR(max),
    	@dirdata VARCHAR(1000),
    	@servername VARCHAR(1000),
    	@strdata VARCHAR(1000),
    	@xmlProlog VARCHAR(1000)
    		
    Set @xmlProlog = '<?xml version="1.0" encoding="UTF-8" ?>'
    Set @dataCollected = 'select isnull(cast(persid as varchar(11)), '''') as persid, isnull(cast(PersonalNr as varchar(11)), '''') as PersonalNr, isnull(cast(LastName as varchar(11)), '''') as LastName, isnull(cast(FirstName as varchar(11)), '''') as FirstName, isnull(cast(TelNr as varchar(11)), '''') as TelNr, isnull(cast(RoomLastACFK as varchar(11)), '''') as Location from Eos93.dbo.Person	order by PersID asc FOR XML PATH(''record''), ROOT(''ROOT'')'
    Set @dataCollected = 'SELECT ''' + @xmlProlog + ''' + (' + @dataCollected + ')'
    
    Set @dirdata = 'C:\test\data.xml'
    
    Set @servername = 'WIN-2CGK\SQLEXPRESS'
    
    Set @strdata = 'bcp "'+ @dataCollected+'" queryout '+@dirdata+' -c -T -S '+@servername+''
    
    EXEC xp_cmdshell @strdata	
    
    GO


    • Edited by Phyxius7 Saturday, April 14, 2018 8:22 AM
    Saturday, April 14, 2018 8:21 AM
  • @Visakh16

    Thank you for your reply. It was very helpful.

    The result is almost correct, unfortunately the double quotes are being removed from the xml prolog.

    Do you have any suggestion for preserving the double quotes?

    The result looks like this:

    <?xml version=1.0 encoding=UTF-8 ?>

    but it should look like this:

    <?xml version="1.0" encoding="UTF-8" ?>

    Cleaned up the code a little bit and included you suggestion:

    Use Eos93
    
    DECLARE 
    	@dataCollected VARCHAR(max),
    	@dirdata VARCHAR(1000),
    	@servername VARCHAR(1000),
    	@strdata VARCHAR(1000),
    	@xmlProlog VARCHAR(1000)
    		
    Set @xmlProlog = '<?xml version="1.0" encoding="UTF-8" ?>'
    Set @dataCollected = 'select isnull(cast(persid as varchar(11)), '''') as persid, isnull(cast(PersonalNr as varchar(11)), '''') as PersonalNr, isnull(cast(LastName as varchar(11)), '''') as LastName, isnull(cast(FirstName as varchar(11)), '''') as FirstName, isnull(cast(TelNr as varchar(11)), '''') as TelNr, isnull(cast(RoomLastACFK as varchar(11)), '''') as Location from Eos93.dbo.Person	order by PersID asc FOR XML PATH(''record''), ROOT(''ROOT'')'
    Set @dataCollected = 'SELECT ''' + @xmlProlog + ''' + (' + @dataCollected + ')'
    
    Set @dirdata = 'C:\test\data.xml'
    
    Set @servername = 'WIN-2CGK\SQLEXPRESS'
    
    Set @strdata = 'bcp "'+ @dataCollected+'" queryout '+@dirdata+' -c -T -S '+@servername+''
    
    EXEC xp_cmdshell @strdata	
    
    GO

    try like this and see

    Use Eos93
    
    DECLARE 
    	@dataCollected VARCHAR(max),
    	@dirdata VARCHAR(1000),
    	@servername VARCHAR(1000),
    	@strdata VARCHAR(1000),
    	@xmlProlog VARCHAR(1000)
    		
    Set @xmlProlog = '<?xml version=""1.0"" encoding=""UTF-8"" ?>'
    Set @dataCollected = 'select isnull(cast(persid as varchar(11)), '''') as persid, isnull(cast(PersonalNr as varchar(11)), '''') as PersonalNr, isnull(cast(LastName as varchar(11)), '''') as LastName, isnull(cast(FirstName as varchar(11)), '''') as FirstName, isnull(cast(TelNr as varchar(11)), '''') as TelNr, isnull(cast(RoomLastACFK as varchar(11)), '''') as Location from Eos93.dbo.Person	order by PersID asc FOR XML PATH(''record''), ROOT(''ROOT'')'
    Set @dataCollected = 'SELECT ''' + @xmlProlog + ''' + (' + @dataCollected + ')'
    
    Set @dirdata = 'C:\test\data.xml'
    
    Set @servername = 'WIN-2CGK\SQLEXPRESS'
    
    Set @strdata = 'bcp "'+ @dataCollected+'" queryout '+@dirdata+' -c -T -S '+@servername+''
    
    EXEC xp_cmdshell @strdata	
    
    GO


    Then you will get your desired result


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page



    • Edited by Visakh16MVP Saturday, April 14, 2018 8:54 AM
    • Marked as answer by Phyxius7 Saturday, April 14, 2018 8:57 AM
    Saturday, April 14, 2018 8:24 AM
  • Doubling up on the quotes. Should have seen that one coming.

    Thanks again Visakh16, work like a charm.

    Saturday, April 14, 2018 8:31 AM
  • Doubling up on the quotes. Should have seen that one coming.

    Thanks again Visakh16, work like a charm.

    welcome

    Feel free to mark as answer to close the thread and to benefit others who follow the thread


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Saturday, April 14, 2018 8:32 AM