Power BI Report Server dbo.Catalog table

[Anonymous]

HI,

I'm trying converting content data from dbo.Catalog table of my Power Bi Report Server Database, but conversion of data about type = 13 (pbix files) return me an issue:

XML parsing line 1 character 3, illegal xml character

Could someone help me?

thanks!

Lorenzo

[v-yulgu-msft]

Hi @Anonymous,

Not sure what SQL query you were using that resulted above error. Please try below query:

WITH ItemContentBinaries AS
(
  SELECT
     ItemID,Name,[Type]
    ,CASE Type
       WHEN 2 THEN 'Report'
       WHEN 5 THEN 'Data Source'
       WHEN 7 THEN 'Report Part'
       WHEN 8 THEN 'Shared Dataset'
	   When 13 Then 'Power BI Report'
       ELSE 'Other'
     END AS TypeDescription
    ,CONVERT(varbinary(max),Content) AS Content
  FROM ReportServer.dbo.Catalog
  WHERE Type in (2,5,7,8,13)
),

--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
  SELECT
     ItemID,Name,[Type],TypeDescription
    ,CASE
       WHEN LEFT(Content,3) = 0xEFBBBF
         THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
       ELSE
         Content
     END AS Content
  FROM ItemContentBinaries
)
--The outer query gets the content in its varbinary, varchar and xml representations...
SELECT
   ItemID,Name,[Type],TypeDescription
  ,Content --varbinary
  ,CONVERT(varchar(max),Content) AS ContentVarchar --varchar
  ,CONVERT(xml,Content) AS ContentXML --xml
FROM ItemContentNoBOM

Reference: Extract RDL (XML) from the ReportServer database

Best regards,
Yuliana Gu

[KBO]

I have some supplements:

1= Folder

3 = Templates

🙂

Best Kathrin

[FinditEZ]

Hi @v-yulgu-msft,

That is the precise query that generates the error @Anonymous is reporting.   Note that the BOM for a Power BI document contents is different then the legacy SSRS reports, datasources and shared datasets.

Is there a different CASE WHEN clause to strip off the unique BOM characters used for Power BI contents stored in SQL Server reports catalog?

Thank you,

Ken

[FinditEZ]

Hi @v-yulgu-msft,

That is the precise query that generates the error @Anonymous is reporting.   Note that the BOM for a Power BI document contents is different then the legacy SSRS reports, datasources and shared datasets.

Is there a different CASE WHEN clause to strip off the unique BOM characters used for Power BI contents stored in SQL Server reports catalog?

Thank you,

Ken

[mgmeyer]

I'm not sure exactly what you are trying to do, are you explicitly writing queries to modify the data that is in the ReportServer Catalog table? What are you trying to achieve by this? Doing something like this is not supported and could put you data/catalog in a bad state and potential break you server installation.

[Anonymous]

It's worth remembering that the Binary in the Content column for a PBIX file is actually the PBIX file itself, which is a ZIP file. So I'm pretty sure you can't meaningfully parse it as XML.