Parsing a lange XML data-column from one server to another

• kaj

  SSCommitted

  Points: 1612

  More actions

  July 19, 2022 at 8:17 pm

  #4065581

  I'm no expert on Powershell scripts by a long shot, but I think you should try to do an explicit cast to NVARCHAR(MAX) or XML on your insert command.

  As I have demonstrated in a previous reply there are issues with literals longer than 4000/8000 characters. Jeff Moden confirmed this and stated that this could be prevented by casting it to varchar(max), nvarchar(max), xml.

  I'll use your test statement to demonstrate what I mean:

  #$InsertQuery2 = "INSERT INTO XML_REPORT_ALL ( logdate, Report, runnum, servername ) VALUES ( '07/11/2022 11:43:12',CAST('<CMDB>hallo</CMDB>' AS XML),'5001','SQLDB1P' ) "

  I'm aware that this means you can't just do a simple -join on all the row's properties and must process each property individually. But I'm sure you'll be able to overcome that hurdle.

• Ed B

  Hall of Fame

  Points: 3682

  More actions

  July 19, 2022 at 10:42 pm

  #4065614

  The first suggestion was to explicitly cast or convert the Report column to xml or to try varchar/nvarchar. Have you tried that? Does something like this work? or give a different error?

  $SelectQuery = @" 
  select logdate, runnum, servername, cast(Report as xml) as Report from.[dbo].[XML_REPORT] where RunNum = (select Max(RunNum) from [DBAmanagment].[dbo].[XML_REPORT] ) 
  "@ 
  
  $SelectQuery = @" 
  select logdate, runnum, servername, convert(varchar(max),Report) as Report from.[dbo].[XML_REPORT] where RunNum = (select Max(RunNum) from [DBAmanagment].[dbo].[XML_REPORT] ) 
  "@

  • This reply was modified 2 years, 8 months ago by  Ed B.