Code Project

Link Unit

Monday, September 11, 2017

working with report model

To create a shared data source

  1. In Solution Explorer, right-click Shared Data Sources, and then select Add New Data Source.
  2. In the Name box, type: RMS.
  3. In the Type drop-down list, select Report Server Model.
  4. In the Connection string area, type: Server=<>; datasource=<>.
  5. Select Credentials.
  6. Select Use Windows Authentication (Integrated Security) and then click OK.
    The data source appears in the Shared Data Sources folder in Solution Explorer.
  7. On the File menu, click Save All.

XML parsing: line 1, character 75, illegal name character

When saving strings to XML, or when trying to extract text within tags it important to escape invalid characters . The following table shows the invalid XML characters and their escaped equivalents.

Invalid XML Character Replaced With
<                          <
>                          >
" "
' '
& &

if we try following code in SQL window, where we are trying to extract text from within html tags.

declare @v varchar(40)
Set @v='a & b'
Select cast(@v as XML).value('.','varchar(max)')

we will receive error like "XML parsing: line 1, character xx, illegal name character"

Solution: 

declare @v varchar(40)
Set @v='a & b'
Select cast(replace(replace(replace(@v,'>','><![CDATA['),'</',']]></')+']]>','<![CDATA[]]>','') as XML).value('.','varchar(max)')

As we know CDATA section is "a section of element content that is marked for the parser to interpret as only character data, not markup." so we will include CDATA in such a way that text is inside it and parsing of it wouldn't result in error. 

Hope it helps