CODEWITHSUNDEEP
c#sql-serverxml
wouter de jong
wouter de jong

Reputation: 597

Illigal qualified name character with XML parsing into SQL using C#

I am trying to filter a column in my table using a query with XML parsing. This query is made into C# and I'm using SQLDataAdapter to perform the query. My code is the following:

string sqlstring = $@"select * from [AS_GOV_PS_oud] inner join [AS_GOV_PStest13_1_2017v2]
                        on   
                                [AS_GOV_PStest13_1_2017v2].[I/O name]        = 'ST' where
cast('<a>' + replace([AS_GOV_PS_oud].Interconnection, '\""', ' </ a >< a > ') + ' </ a > ' as xml).query('for $x in / a order by $x return string($x)').value(' / ', 'varchar(max)') =                            
cast('<a>' + replace([AS_GOV_PStest13_1_2017v2].Interconnection, '\""', ' </ a >< a > ') + ' </ a > ' as xml).query('for $x in / a order by $x return string($x)').value(' / ', 'varchar(max)') ";

string cn = ConfigurationManager.ConnectionStrings["Scratchpad"].ConnectionString;
using (SqlConnection myConnection = new SqlConnection(cn))
            {
                SqlDataAdapter dataadapter = new SqlDataAdapter(sqlstring, myConnection);
            }

The error code is the following:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: XML parsing: line 1, character 33, illegal qualified name character

Upvotes: 0

Views: 1895

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Your code is super ugly!

With this you break almost any rule how one should do this. But nevertheless your question can be answered: blanks in the wrong place

Try this:

SELECT CAST('<a>test</a>' AS XML) --works

SELECT CAST('< a>test</a>' AS XML) --exception due to the blank in '< a>'

SELECT CAST('<a>test</ a>' AS XML) --exception due to the blank in '</ a>'

My suggestion: Think about the whole thing once again... What are you really trying to achieve? Using such a construction as condition for a join is an incredibly wrong approach...

Re-Think it and place a new quesiton, where you set up some sample data, describe your needs and provide the expected output.

Place a link to this question and - if you follow these sugggestions - SO's armada will rush in and will help you to find a better solution than this...

Upvotes: 1

Related Questions