Reckoned Force

Full Service Business Data Solutions

XML parsing errors

By

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 illegal character in the xml tag name.
This is when there is a space in the xml tag
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,' ','')		
		     ,'&','&amp;')
		    ,'''','&#39;')
		   ,'"','&quot;')
		  ,'<','&lt;')
		 ,'>','&gt;')
		))                      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.

Comments

  1. 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 (‘.’,’-‘,’/’,’,’,’&’,””,'”‘,’< ','>‘)