Post reply

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

  • 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. 🙂

  • 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, 4 months ago by  Ed B.

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply