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 · Answer 1 · 2013-09-16T16:12:13.427

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 '13 at 16:12

answered Sep 13 '13 at 20:33

Chad Miller

40,127
3
30
34

out-string is also truncating the data – Shavez Sep 16 '13 at 06:01
Not out-string. See my revised example comparing output of out-file to your set-content code. – Chad Miller Sep 16 '13 at 16:12
I am still getting newline and extra spaces. I have a huge data of XML, Is that an issue? – Shavez Sep 17 '13 at 04:45
Perhaps there a line break in the source. You can run it through join: – Chad Miller Sep 17 '13 at 10:27
$xml = invoke-sqlcmd -inputfile ...; $xml = $xml -join – Chad Miller Sep 17 '13 at 10:34
Same issue , Still I am getting the line breaks – Shavez Sep 17 '13 at 10:45
$variable=invoke-sqlcmd -inputFile $inputFilePath -serverinstance $dbHostName -database $dbName -username $userName -password $password | Format-table -hide -wrap -AutoSize |out-string -width 4096 $variable = $variable -join "" – Shavez Sep 17 '13 at 10:46
How about this $xml = invoke-sqlcmd -inputFile $inputFilePath -serverinstance $dbHostName | select -expandproperty Column1 $xml = $xml -replace "`n" $xml | set-content -Path $outputFilePath – Chad Miller Sep 17 '13 at 14:24

score 0 · Answer 2 · edited May 23 '17 at 11:52

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 '17 at 11:52

Community

1
1

answered May 07 '16 at 21:01

vitrilo

1,437
16
20

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

2 Answers2