Using XML data in Power BI from SQL Server Database

If you are aware about Power BI Data sources, then you must be knowing SQL Server as one of the most generic data sources. In SQL server if all the values are directly present under each of the fields, then it is super easy to create a dataset from SQL Server tables. But it would be tricky if the data is available in XML format in the database column. If you see the below example, the 2nd field “XMLData”, is storing xml values in the database table. This data can not be used directly in Power BI as a Dataset to create the Report.

Export batch

SQL database can be connected to Power BI in 2 different ways :

  1. Import 
  2. Direct Query

Import Option imports selected tables into Power BI Desktop.Then Power BI uses the columns from these selected tables for Data Visualization.

Direct Query option is used to create Visualization from current/live data.In this case, data is not imported in Power BI, while an interaction is created between Visualization and database tables. 

Below Steps can be used to use xml data, using Direct Query Option :

  1. Go to File tab and Click on Get Data Option. Select SQL Server Database as the data source :
Get data

2. In SQL server Database config window provide Server name and database name. Choose Direct Query Option. Provide the query to be used to extract the data from SQL database table. Click on OK :

SQL server database

3. In next window, provide database Username and Password, to which database has to be connected and Click on Connect Button :

test server123

4. Query with database columns will be available on the right side of the window, as a dataset. Right Click on dataset and Click on Edit Query Option :

Build visuals with your data
Edit query

5. In Power Query Editor window, Use Parse Option from Transform Tab, to Parse xml data and expand the Table after parsing xml data column. There are multiple transformation options available under Transform Tab, which can be used to apply on parsed xml data, as per the requirement :

transform tab
Applied steps

6. Once all the transformations are done, Click on Close & Apply Option from the Home Tab. All the fields from Parsed XML will be available under the dataset on the right side. You can Rename the dataset name also :

click on close and apply
tab tools

Now, all the fields from new dataset can be used to create data visualization, as shown below :

Table tools to create data visualization

Once this Report is published in Power BI Service portal, as we are using direct query mode, data needs to be refreshed in the portal as well, when new data arrives. To update the latest data, schedule refresh must be created in the Service portal.

This is how xml data from the database table can be visualized in Power BI Report.

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *