Today we will talk about two XML parsing errors. XML Parsing: illegal qualified name character and XML Parsing: equal expected
I was working on some unusual XML format to match an input XML format of an existing application in SQL Server. I happen to get these two errors and they have rather very easy fix. But I thought to share it with you in case you have same error and decide the got to the web instead of figuring what the error is what you should do is avoid space and illegal characters from your XML element name. Yap how easy that could get.
<root> <col11_col21> <col11>val11</col11> <col21>val21</col21> <col31>val31</col31> <col41>val41</col41> </col11_col21> <col12_col22> <col12>val12</col12> <col22>val22</col22> <col32>val32</col32> <col42>val42</col42> </col12_col22> </root> |
This is when there illegal character in the xml tag name.
This is when there is a space in the xml tag.
And if you if you are interested on the SQL script to get the format similar to the XML above you can write your script as:
;WITH xmlCTE as( SELECT top (200) ltrim( rtrim( replace( replace( replace( replace( replace( replace(Col1 + '_' + Col2,' ','') ,'&','&') ,'''',''') ,'"','"') ,'<','<') ,'>','>') )) as xmltag, isnull(Col1, '') as Col1, isnull(Col2, '') as Col2, isnull(Col3, '') as Col3, isnull(Col4, '') as Col4 FROM Table1 where YourCriteria1=something ) SELECT CAST(( SELECT CAST( '<' + parent.xmltag + '>'+ (SELECT child. Col1, child. Col2, child. Col3, child. Col4 FROM xmlCTE child WHERE child.xmltag = parent.xmltag FOR XML PATH('')) + '</' + parent.xmltag + '>' AS xml) FROM xmlCTE parent for xml PATH(''), ROOT('counselors') ) AS XML) as xmloutput; go |
Check out http://www.w3schools.com for more XML tutorials.
I was excited to see your post, I get these exact errors when downloading from Analysis Services Cube into Excel… I don’t know what determines the xml tag. I think it is generated automatically? Not sure how I would change it.
Hello Elizabeth, Although your question is on Analysis services try to remove other illegal characters before building your cube. Some of these characters are (‘.’,’-‘,’/’,’,’,’&’,””,'”‘,’<','>‘)