Exporting data that is stored as XML data type to XML file

• piet_dj

  SSC-Addicted

  Points: 428

  More actions

  June 28, 2012 at 4:55 am

  #263332

  Hi,

  I have a table containing rows of XML data stored in the SQL XML Data type. I'd like to export the complete XML in each row to one concatenated XML file, then add some final body tags around the lot - is this possible using T-SQL?

  I have used bcp to successfully do this for one row to one XML file, but when trying multiple rows to one XML file the charcters aren't readable. This is what I have used so far:

  declare @cmd nvarchar(255);

  select @cmd = '

  bcp "SELECT TOP 10 XMLColumn from DB1.[dbo].[XMLFiles] " ' +

  'queryout "C:\sample.xml" -S myserver -T -w -r -t';

  exec xp_cmdshell @cmd;

  go

  Can anyone help?

• okbangas

  SSChampion

  Points: 11773

  More actions

  June 28, 2012 at 6:40 am

  #1506563

  Like this?

  declare @cmd nvarchar(255);

  select @cmd = '

  bcp "select cast(cast(XMLColumn as nvarchar(max)) as xml) from DB1.[dbo].[XMLFiles] for xml path('')" ' +

  'queryout "C:\sample.xml" -S myserver -T -w -r -t';

  exec xp_cmdshell @cmd;

  go

  ---

  
  Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

  Concatenating Row Values in Transact-SQL[/url]