XML data coming in newline using invoke-sql command in powershell

Question

I am using a powershell script which will return an XML from a sqlquery of FOR XML EXPLICIT type. Now, the query returned the XML in a column with header and dashed lines

XML 32776498797309
---------------------
<data>asdsafafaf<\data><value>dfsasfasfdfasdf....
dfdsfsdgregrge<\value><value>asdfasdfadfadfsda<\v
alue>

Here, some how we were able to remove header , dashed line and truncating of data. but still the data comes in new line so if we open the XML it throws error because at some places the tag gets distributed as shown above. Basically, new line issue is there. We tried -width 4096 but since XML is huge it is not proper. Please help, stuck big time.

Query used :

 invoke-sqlcmd -inputFile $inputFilePath -serverinstance $dbHostName
                  -database $dbName -username $userName -password $password
                  | Format-Table -hide -Wrap -AutoSize 
                  | Out-File -filePath $outputFilePath -width 4096

Chad Miller · Accepted Answer · 2013-09-16 16:12:13Z

2

Try using set-content instead of out-file. Out-file will use the host/console formatting while set-content does not. You'll need to handle outputting XML to string

invoke-sqlcmd ....| select -expandproperty XML | set-content -path $outputFilePath

Edited. Added full working example:

#Contents of my test inputfile.sql:
SELECT CAST ('<EVENT_INSTANCE>
  <EventType>CREATE_TABLE</EventType>
  <PostTime>2011-04-26T15:05:21.333</PostTime>
  <SPID>56</SPID>
  <ServerName>Z001\SQL1</ServerName>
  <LoginName>Contoso\u00</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>AdventureWorksDW2008R2</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>AdventureWorksDWBuildVersion</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>CREATE TABLE [dbo].[AdventureWorksDWBuildVersion] (
[DBVersion] [nvarchar] (50) NULL,
[VersionDate] [datetime] NULL 
) ON [PRIMARY];
</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>' AS XML)



$inputFilePath = "C:\Temp\inputfile.sql"
$dbHostName = "$env:computername\sql1"
$outputFilePath = "C:\Temp\output.txt"

#Notice Extra Line Breaks
invoke-sqlcmd -inputFile $inputFilePath -serverinstance $dbHostName | 
    Format-Table -hide -Wrap -AutoSize |
    Out-File -filePath $outputFilePath -width 4096

#No Extra line breaks using set-content
invoke-sqlcmd -inputFile $inputFilePath -serverinstance $dbHostName | select -expandproperty Column1 | set-content -Path $outputFilePath

edited Sep 16, 2013 at 16:12

answered Sep 13, 2013 at 20:33

Chad Miller

42.4k4 gold badges33 silver badges34 bronze badges

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

8 Comments

Shavez Over a year ago

out-string is also truncating the data

Chad Miller Over a year ago

Not out-string. See my revised example comparing output of out-file to your set-content code.

Shavez Over a year ago

I am still getting newline and extra spaces. I have a huge data of XML, Is that an issue?

Chad Miller Over a year ago

Perhaps there a line break in the source. You can run it through join:

Chad Miller Over a year ago

$xml = invoke-sqlcmd -inputfile ...; $xml = $xml -join

|

Community · Accepted Answer · 2017-05-23 11:52:37Z

0

Use -MaxCharLength parameter of Invoke-SQLCMD command. By default it 4000. it will truncate big XMLs.

See Invoke-SqlCmd doesn't return long string?

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered May 7, 2016 at 21:01

vitrilo

1,51516 silver badges20 bronze badges

Collectives™ on Stack Overflow

XML data coming in newline using invoke-sql command in powershell

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related