Stored Procedure and XML

Question

I get this error:

The error description is 'Only one top level element is allowed in an XML document.'.
Could not find prepared statement with handle 0.
The XML parse error 0xc00ce555 occurred on line number 1, near the XML text "<value1>34</value1><value1>33</value1><value1>32</value1>".
The statement has been terminated.

This is Stored Procedure call:

 public bool HideFromList(string commentList, bool state)
{
//commentList =<values><value1>34</value1><value1>33</value1><value1>32/value1></values>
  using (SqlConnection cn = new SqlConnection(this.ConnectionString))
 {
       SqlCommand cmd = new SqlCommand("VisibleFromList", cn);
       cmd.CommandType = CommandType.StoredProcedure;
       cmd.Parameters.Add("@XMLDoc", SqlDbType.Xml).Value = commentList;
       cmd.Parameters.Add("@state", SqlDbType.Int).Value = state;
       cn.Open();
       int ret =  cmd.ExecuteNonQuery();

       return (ret == 1);
 }

}

This is my Stored Procedure:

ALTER PROCEDURE dbo.VisibleFromList
(
    @XMLDoc   xml,
    @state     BIT
)

AS
BEGIN


DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XMLDoc 


UPDATE tbh_Comments
SET Visible = @state WHERE 
CommentID IN (SELECT * FROM OPENXML(@docHandle, '/values/value1', 2) WITH (value1 INT '.'))


END

But If I Modify SP with embedded input string it does work:

ALTER PROCEDURE dbo.VisibleFromList
(
    @XMLDoc   xml,
    @state     BIT
)

AS
BEGIN


DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, 
'<values>
 <value1>33</value1>
<value1>34</value1>  
</values>'

UPDATE tbh_Comments
SET Visible = @state WHERE 
CommentID IN (SELECT * FROM OPENXML(@docHandle, '/values/value1', 2) WITH (value1 INT '.'))


END

How to make it work with input parameter?

Answer 1

Are you positive your commentList is as you describe?

//commentList =<values><value1>34</value1><value1>33</value1><value1>32/value1></values>

If your list is indeed similar to that, it should work.

According to your error, it appears the <values> element may not exist:

near the XML text "<value1>34</value1><value1>33</value1><value1>32</value1>".

Answer 2

If you're doing some kind of looping I've found doing it like this is helpful:

XElement entityListElement = new XElement("values");
XElement entityElement = new XElement(
                    "value",
                    new XElement("value1", "15"));
entityListElement.Add(entityElement);

And then pass in entityListElement.ToString().

This also makes it (arguably) easier to make changes and has .NET do the XML generation for you.

Answer 3

I recommend you convert your procedures to use XML Data Type Methods. Unlike the sp_prepare_document stuff, these are implemented natively by the SQL engine and they interact better with queries, produce better error messages and you don't run the risk of leaking document handles.

So in your procedure you could do something like:

declare @XMLDoc xml;
select @XMLDoc =N'<values>
 <value1>33</value1>
<value1>34</value1>  
</values>';


UPDATE tbh_Comments
SET Visible = @state WHERE 
CommentID IN (
    SELECT v.value('.', 'INT') as CommentID
    FROM @XMLDoc.nodes('/values/value1') t(v))