Displaying XML

[ammanviii]

Hey @9whirls , I want to display the output that I get from a database using webCommander like this:

RowID DisplayHint DateTime Date Day DayOfWeek DayOfYear Hour Kind Millisecond

---

84355 DateTime Monday, January 01, 2018 12:17:52 AM 1/1/2018 12:00:00 AM 1 Monday 1 Local 15
84356 DateTime Monday, January 01, 2018 12:47:50 AM 1/1/2018 12:00:00 AM 1 Monday 1 Local 781
84357 DateTime Monday, January 01, 2018 1:18:27 AM 1/1/2018 12:00:00 AM 1 Monday 1 1 Local 328
84358 DateTime Monday, January 01, 2018 1:47:53 AM 1/1/2018 12:00:00 AM 1 Monday 1 1 Local 812
84359 DateTime Monday, January 01, 2018 2:17:58 AM 1/1/2018 12:00:00 AM 1 Monday 1 2 Local 62
84360 DateTime Monday, January 01, 2018 2:47:39 AM 1/1/2018 12:00:00 AM 1 Monday 1 2 Local 672

I followed the vmPowerOn.ps1 script to display my output using foreach and XML :

foreach ($i in ($sqlOutput | sort)){ $sqlQueryXml = $i | select RowID,DateTime, Date, Day, DayOfWeek, DayOfYear, Hour| convertTo-xml -as string -notypeinformation $sqlQueryXml = $sqlQueryXml.trimstart('<!--?xml version="1.0"?-->') $sqlQueryXml = $sqlQueryXml.replace('Object','SQLQuery') $sqlQueryXml }

But this is how it displays like, what should I change for it to display one property and it's value in one column like above.

Thank you!

[9whirls]

To achieve this, please refer to https://github.com/vmware/webcommander/blob/master/powershell/showHistory.ps1.
Understand how this command output records.

You also have to add something in webcmd.xsl for your data. Refer to
https://github.com/vmware/webcommander/blob/master/www/webCmd.xsl#L239 through line 261

[9whirls]

You could also use format-table to format the data and then use write-stdout to output it.

[ammanviii]

Hey @9whirls , thank you for suggesting showHistory. I believe it's displayed properly using datatables over just using writeStdout. I added into the webcmd.xsl the XSL to make it look like showHistory but I didn't how to make the data I get to display like showHistory. Do I have to convert the data I get from the query into a XML file or what do you suggest I try?

[9whirls]

Have a look at https://github.com/vmware/webcommander/blob/master/www/webCmd.xsl#L239 through line 262. You need to add a similar section there for your data

[9whirls]

I suggest using the walnut version of webcommander.
That way, you could simply output the data as "dataset". Have a look at https://github.com/9whirls/webcommander_walnut/blob/master/powershell/vSphere/object.ps1#L37

[ammanviii]

I added this to webCmd.xsl

				<xsl:when test="name() = 'sqlXSL'">
						<hr class="separator"/>
						<center><table id="hisTable">
						</table></center>
						<script>
            var dataset = [<xsl:value-of select="text()" />];
							$(function(){
								var t = $('#hisTable').dataTable({
                bDeferRender: true,
                data: dataset,
                "columns": [
                  { title: "Row" },
                  { title: "DateTime" },
                  { title: "Date" },
                  { title: "Day" },
                  { title: "DayOfWeek" },
                  { title: "DayOfYear" },
                  { title: "Hour" }
                ],
                "order": [[ 0, "desc" ]]
              });
							});
						</script>
					</xsl:when>

And I added this to my script

`$stringBuilder = New-Object System.Text.StringBuilder
$null = $stringBuilder.append("<![CDATA[")

$total = ($sqlOutput | measure-object).count
$i = 0

$records | select -first 50000 {
$record = "[ ""$RowID"", ""$DateTime"", ""$Date"", ""$Day"", ""$DayOfWeek"", ""$DayOfYear"", ""$Hour""],"
$null = $stringBuilder.Append($sqlOutput )
}
$null = $stringBuilder.remove($stringBuilder.length - 1 , 1)
$null = $stringBuilder.append("]]>")
write-output $stringBuilder.toString()`

What do you think I should add to make it work

[ammanviii]

I'm checking out webcommander_walnut but I need to finish this by this week. Then I'll try to transfer what I did to walnut after.

[9whirls]

You missed the tag name in you script. It should be
$null = $stringBuilder.append("<sqlXSL><![CDATA[")
...
$null = $stringBuilder.append("]]></sqlXSL>")