missing elements while passing XML from .NET to SQL 2005

19 Mar 2009 11:58 AM

Pradeep

Hi,

I am calling an SP from my .NET class.

SqlParameter prmMessage = new SqlParameter();
                prmMessage.ParameterName = "@iMessage";
                prmMessage.SqlDbType = SqlDbType.Xml;
                prmMessage.Direction = ParameterDirection.Input;
                prmMessage.Value = xmldoc.InnerXml;
                prmMessage.Size = xmldoc.InnerXml.Length;

The SP takes a parameter which is of XML type and the value of this XML
should go into a column of table which is of XML type. THis is happening. But
the issue is some of the elements are missing in the XML which is stored in
the column. Only elements which doesn't have value and also those elements
which doesn't have value but have attribute values. A simple scenario is
mentined below

SP Input:
-------
<element1>
<element2>2</element2>
<element3>3</element3>
<element4 id=1/>
<element5/>
</element1>

SP Output:
---------------
<element1>
<element2>2</element2>
<element3>3</element3>
</element1>

Please let me know whats the problem

Rgrds,
Pradeep

20 Mar 2009 8:20 AM

Joe Fawcett

That's very strange. I usually pass the XML data as shown in this article,
using the SqlXml class,
http://www.developer.com/net/net/article.php/3406251.
However your method should work.
Is your procedure doing anything to the XML other than storing it, is there
an XML schema on the column?

--

Joe Fawcett (MVP - XML)
http://joe.fawcett.name

Show quoteHide quote

"Pradeep" <Prad***@discussions.microsoft.com> wrote in message
news:C29CD38B-E265-4D4F-9E96-C0F761727927@microsoft.com...
> Hi,
>
> I am calling an SP from my .NET class.
>
> SqlParameter prmMessage = new SqlParameter();
>                prmMessage.ParameterName = "@iMessage";
>                prmMessage.SqlDbType = SqlDbType.Xml;
>                prmMessage.Direction = ParameterDirection.Input;
>                prmMessage.Value = xmldoc.InnerXml;
>                prmMessage.Size = xmldoc.InnerXml.Length;
>
> The SP takes a parameter which is of XML type and the value of this XML
> should go into a column of table which is of XML type. THis is happening.
> But
> the issue is some of the elements are missing in the XML which is stored
> in
> the column. Only elements which doesn't have value and also those elements
> which doesn't have value but have attribute values. A simple scenario is
> mentined below
>
> SP Input:
> -------
> <element1>
> <element2>2</element2>
> <element3>3</element3>
> <element4 id=1/>
> <element5/>
> </element1>
>
> SP Output:
> ---------------
> <element1>
> <element2>2</element2>
> <element3>3</element3>
> </element1>
>
> Please let me know whats the problem
>
> Rgrds,
> Pradeep

21 Mar 2009 1:36 AM

Michael Coles

Is your XML really not well-formed as shown in the example? What happens if
you make it well-formed like this?

<element1>
<element2>2</element2>
<element3>3</element3>
<element4 id="1" />
<element5/>
</element1>

Does it succeed then?

--

========
Michael Coles
"Pro T-SQL 2008 Programmer's Guide"
http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X

Show quoteHide quote

"Pradeep" <Prad***@discussions.microsoft.com> wrote in message
news:C29CD38B-E265-4D4F-9E96-C0F761727927@microsoft.com...
> Hi,
>
> I am calling an SP from my .NET class.
>
> SqlParameter prmMessage = new SqlParameter();
>                prmMessage.ParameterName = "@iMessage";
>                prmMessage.SqlDbType = SqlDbType.Xml;
>                prmMessage.Direction = ParameterDirection.Input;
>                prmMessage.Value = xmldoc.InnerXml;
>                prmMessage.Size = xmldoc.InnerXml.Length;
>
> The SP takes a parameter which is of XML type and the value of this XML
> should go into a column of table which is of XML type. THis is happening.
> But
> the issue is some of the elements are missing in the XML which is stored
> in
> the column. Only elements which doesn't have value and also those elements
> which doesn't have value but have attribute values. A simple scenario is
> mentined below
>
> SP Input:
> -------
> <element1>
> <element2>2</element2>
> <element3>3</element3>
> <element4 id=1/>
> <element5/>
> </element1>
>
> SP Output:
> ---------------
> <element1>
> <element2>2</element2>
> <element3>3</element3>
> </element1>
>
> Please let me know whats the problem
>
> Rgrds,
> Pradeep