SQL Server XML column is string in .NET using SqlDataReader

Question

Why if in SQL Server, I have a column defined as XML type, and in .NET with SqlDataReader/SqlDataAdapter I have a string?

See the example in the screenhsot, cast c1 in XML, but .NET gets a string.

I tried also with XML column, always string.

This my code

SqlCommand sc = new SqlCommand("select *, convert(xml, c1) c6 from tempdb..t1", conn);

DataTable dt = new DataTable();
new SqlDataAdapter(sc).Fill(dt);

How can I recognize a XML result to cast only when necessary?

That's how it reads it. You can use GetSqlXml if you have a reader learn.microsoft.com/en-us/dotnet/api/… but otherwise it will just turn it into a string. — Charlieface
– Charlieface, Commented Jul 9, 2024 at 13:07
reader.GetDataTypeName tells you the type. By the way your code in the screenshot rather ominously uses both SqlDataAdapter` and SqlDataReader which means you are probably executing the command twice. You are also missing using to dispose everything. — Charlieface
– Charlieface, Commented Jul 9, 2024 at 14:23

elle0087 · Accepted Answer · 2024-07-09 20:15:24Z

0

SOLUTION

as suggested by @Charlieface, instead of

dr[n].GetType() // get System.String (.net type)

i used

dr.GetDataTypeName(n) // get xml (sql type)

answered Jul 9, 2024 at 20:15

elle0087

92211 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

SQL Server XML column is string in .NET using SqlDataReader

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related