ExecuteXMLReader fails with valid xml from sql server. Why?

(From a .net class using sqlcommand)
A stored proc that takes 2 input params is firing on the server (i've
checked using sql profiler). The sp in question produces valid xml (i've
checked by creating a sqlxml virutal dir and allowing url queries - the
result is valid in IE and therefore well formed xml)
The stored proc in question is not a single "select X for xml ..." query. It
is made up of fragments
example of results
<root>
<subnode>
select x ... for xml ...
</subnode>
<subnode>
select x ... for xml ...
</subnode>
...
</root>
Why won't ExecuteXmlReader work with the results?
XR = CMD.ExecuteXmlReader()
(where xr is an xml reader)
this line comes back with the error
"Invalid command sent to ExecuteXmlReader. The command must return an Xml
result."
The command is not invalid! I can see it executing on sql. The result is
valid xml.
I have also tried using the sqlxmlcommand but it claimed I was not passing
parameters in (which I was) so I gave up on that.
I am beginning to hate .net
The concept is great but there is too much pain
trying to connect things together...
Any ideas...?
CODE--
Dim StartDate As String =
ArgsDoc.SelectSingleNode("//Parameter[@.name='startDate']/@.value").Value
Dim EndDate As String =
ArgsDoc.SelectSingleNode("//Parameter[@.name='endDate']/@.value").Value
CMD = New SqlCommand
CMD.Connection = CN
Select Case SubType
Case Nothing
CMD.CommandText = "XXXXX"
CMD.CommandType = CommandType.StoredProcedure
Dim Param As New SqlParameter
CN.Open()
Try
Param.ParameterName = "@.start_date" : Param.Value =
StartDate : CMD.Parameters.Add(Param)
Param = New SqlParameter
Param.ParameterName = "@.end_date" : Param.Value =
EndDate : CMD.Parameters.Add(Param)
Catch ex As System.Exception
System.Diagnostics.Debug.WriteLine(ex.Message)
Finally
End Try
Case Else
'do something
End Select
'--
Try
XR = CMD.ExecuteXmlReader() ' * * * * * * LINE THAT FAILS * * *
* * * *
ResultsDoc = New XmlDocument
ResultsDoc.Load(XR)
Catch ex As System.Exception
System.Diagnostics.Debug.WriteLine(ex.Message)
Finally
End Try
END CODE--
In your description, you create a command like this:

> <root>
> <subnode>
> select x ... for xml ...
> </subnode>
> <subnode>
> select x ... for xml ...
> </subnode>
> ...
> </root>
Where did you do such construction? What type of variable or command are you
using?
One easy solution on Yukon is that you can try is store the result in a
"xml" type variable, and then select from it.
For the ExecuteXmlReader() expecting an "XML" typed data back, instead of
any string that looks like "XML".
Thanks.
Xin
"adolf garlic" wrote:

> (From a .net class using sqlcommand)
> A stored proc that takes 2 input params is firing on the server (i've
> checked using sql profiler). The sp in question produces valid xml (i've
> checked by creating a sqlxml virutal dir and allowing url queries - the
> result is valid in IE and therefore well formed xml)
> The stored proc in question is not a single "select X for xml ..." query. It
> is made up of fragments
> example of results
> <root>
> <subnode>
> select x ... for xml ...
> </subnode>
> <subnode>
> select x ... for xml ...
> </subnode>
> ...
> </root>
> Why won't ExecuteXmlReader work with the results?
> XR = CMD.ExecuteXmlReader()
> (where xr is an xml reader)
> this line comes back with the error
> "Invalid command sent to ExecuteXmlReader. The command must return an Xml
> result."
> The command is not invalid! I can see it executing on sql. The result is
> valid xml.
> I have also tried using the sqlxmlcommand but it claimed I was not passing
> parameters in (which I was) so I gave up on that.
> I am beginning to hate .net
> The concept is great but there is too much pain
> trying to connect things together...
> Any ideas...?
>
> CODE--
>
> Dim StartDate As String =
> ArgsDoc.SelectSingleNode("//Parameter[@.name='startDate']/@.value").Value
> Dim EndDate As String =
> ArgsDoc.SelectSingleNode("//Parameter[@.name='endDate']/@.value").Value
> CMD = New SqlCommand
> CMD.Connection = CN
> Select Case SubType
> Case Nothing
> CMD.CommandText = "XXXXX"
> CMD.CommandType = CommandType.StoredProcedure
> Dim Param As New SqlParameter
> CN.Open()
> Try
> Param.ParameterName = "@.start_date" : Param.Value =
> StartDate : CMD.Parameters.Add(Param)
> Param = New SqlParameter
> Param.ParameterName = "@.end_date" : Param.Value =
> EndDate : CMD.Parameters.Add(Param)
> Catch ex As System.Exception
> System.Diagnostics.Debug.WriteLine(ex.Message)
> Finally
> End Try
> Case Else
> 'do something
> End Select
> '--
> Try
> XR = CMD.ExecuteXmlReader() ' * * * * * * LINE THAT FAILS * * *
> * * * *
> ResultsDoc = New XmlDocument
> ResultsDoc.Load(XR)
> Catch ex As System.Exception
> System.Diagnostics.Debug.WriteLine(ex.Message)
> Finally
> End Try
>
> END CODE--

Execute SQL Task w/ XML Output

I have a stored procedure that returns XML and have defined my own "Root" element w/ "ROOT('urlset')" in the stored procedure. When I put this into my Control Flow, I have a subsequent task that takes this XML stream and writes it to a file. The issue I am running into is that SSIS is adding it's own "Root" element before my output. Is there any way of avoiding this?
Unfortunately, the Execute SQL Task always inserts the <ROOT> ... </ROOT> tags when you ask for an XML result set. The typical approach is to strip out the values you want in an XML Task or Script Task.|||

Matt Masson - MSFT wrote:

Unfortunately, the Execute SQL Task always inserts the <ROOT> ... </ROOT> tags when you ask for an XML result set. The typical approach is to strip out the values you want in an XML Task or Script Task.

Hi Matt,

Why? What's the rationale for this behaviour?

-Jamie

|||

Good question.

The task doesn't really parse the input SQL statement (or the results, for that matter), so it appears the tags were added as a simple way to ensure we were always returning a well-formed XML document.

One could argue that this should be optional behaviour, and that we should provide a property which allows you to tell the task to leave the results alone. I've opened a tracking item to consider the change for Katmai.

Feel free to open an item on Connect if you have ideas on how it should work.

~Matt

|||

No need. You beat me to it

Execute Package Task error: Failed to decrypt protected XML node "DTS:Password"

I have a package (PackageA) with an Execute Package Task that execs PackageB. When I run PackageA I get this error on the Execute Package Task :

Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

PackageB has 'EncryptSensitiveWithUserKey' ProtectionLevel. I'm providing passwords in the dtsConfig so I'm guessing I should change it to 'DontSaveSensitive'?

Interestingly, PackageA also has 'EncryptSensitiveWithUserKey' ProtectionLevel, but I don't get an error about PackageA, just on the task that runs PackageB.

(SP1 is installed).

Al C. wrote:

PackageB has 'EncryptSensitiveWithUserKey' ProtectionLevel. I'm providing passwords in the dtsConfig so I'm guessing I should change it to 'DontSaveSensitive'?

Yes, since the data is configured somewhere else, DontSaveSensitive is correct choice.

Al C. wrote:

Interestingly, PackageA also has 'EncryptSensitiveWithUserKey' ProtectionLevel, but I don't get an error about PackageA, just on the task that runs PackageB.

The message is only raised when the package contains the actual protected data - passwords, connection strings that can't be decrypted. If the package had 'EncryptSensitiveWithUserKey' ProtectionLevel, but does not have any sensitive data, you will not see this message.

|||Thanks Michael. That explains it all!