2

Following this tutorial I tried to use PowerShell to retrieve xml data from SQL Server, but I only get one element back.

Here is a query to show the actual data:

enter image description here

But running this script I only get one element back:

$SQLServer = 'MYSERVER,1433'
$SQLDBName = "test"
$Query = 
@'
USE test

SELECT EventLogXML FROM ForwardedEvents

'@
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $Database; Integrated Security = True"
$SqlConnection.open()    
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $Query
$SqlCmd.Connection = $SqlConnection
$xr = $SqlCmd.ExecuteXmlReader()
$xd = New-Object System.Xml.XmlDataDocument
$xd.Load($xr)
$xr.Close()
$SQLConnection.Close()
$xd

$xd only has one element. What am I doing wrong?

---edit I can confirm its only one xml doc by doing $xd.outerxml which reveals the complete doc. It is only one of the thousand or so event xml docs I'm storing in the EventLogXML column.

Improve this question
3
  • Naturally, you'd have one document per row. And since you creating one document only, it's not surprising that you get only information from a single row in it. Did you expect all these separate xmls from all the rows to be combined somehow? If so, how? Commented Aug 1, 2013 at 23:55
  • Hmm, something fundamental I'm missing here. I thought $xd was supposed be an object containing all the rows. Its not? I wanted to return an array of all the xml objects in the column that I could loop through. I have never worked with sql like this before so I think I'm misunderstanding what the script is actually doing. Commented Aug 2, 2013 at 0:13
  • Oops, sorry, my bad. I misread XmlDataDocument as XmlDocument. Please disregard my earlier comment. Commented Aug 2, 2013 at 0:32

3 Answers 3

Reset to default
4

I think that XmlDataDocument is mainly for returning a single xml. Basically if you do in sql select * from bla for xml, auto you then can read it with the ExecuteXmlReader and XmlDataDocument. This is not what you want.

Modifying the example you linked to your needs we'll get somethign like:

$con = New-Object System.Data.SqlClient.SqlConnection
$con.ConnectionString = "Server=.; Database=AdventureWorks2012;Integrated Security=true"
$con.open()

$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = "SELECT Instructions FROM Production.ProductModel WHERE Instructions is not null"
$cmd.Connection = $con

$as = New-Object System.Data.SqlClient.SqlDataAdapter
$ds = New-Object System.Data.DataSet
$as.SelectCommand = $cmd
$as.Fill($ds);

$xmlDocs = $ds.Tables[0] | %{ [xml]$_.Instructions }

Now xmlDocs will contain a list of xml documents, one document per row.

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

1 Comment

This is what I wanted to do. The only reason I was using XmlDataDocument was because I was getting a weird error when assigning the complete "SELECT * FROM TABLE" query to a single variable. My question should have explained this was my initial issue. Well I just tried using a "new" variable to store the dataset and it worked, oops. All of this because I accidentally cast the variable I was using to some weird type before I tried using it to store the dataset.
2

Powershell wraps XML stuff into handy little objects, which you can explore using .Property syntax. If you just look at $xd, powershell by default will only show you the root node.

I don't know the structure of your XML column, but if the root node is called MyRoot, followed by common subnodes called MySub, try something like this:

$xd.MyRoot.MySub

This is just as the linked example shows the need to use $xd.root.Location

Edit

Ok so that is not the problem. Looks like it is by-design to return back only the first row when calling ExecuteXmlReader with a normal select statement (doc here):

if more than one row is returned, the ExecuteXmlReader method attaches the XmlReader to the value on the first row, and discards the rest of the result set

From some basic searching around, this blog post seems to explain the issue the best, and provides a workaround. See also here.

Improve this answer

1 Comment

Thank you very much for responding, but that's not it. It really is only one xml doc. I can confirm this by doing $xd.outerxml which reveals the complete element. I'll add this info as an edit to my post.
0

I may be out to lunch, but couldn't it be because you are declaring the database as $SQLDBName and then trying to connect to $Database in your connectionstring?

Improve this answer

1 Comment

Oh noes! didn't even notice that. I'll correct this tomorrow at work and test.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.