How to convert SQL-Server "Policy result" file from XML to Excel?

David Kříž 1 Reputation point
2021-02-03T15:51:02.867+00:00

After I evaluate some Policy (in "Policy Management" in "Microsoft SQL Server Management Studio") I can see evaluation results. And here I can use the button "Export Results". Unfortunately, this function allows export results only to XML format. But my customer needs it in Excel format. How to convert this "Policy result" file from XML to Excel?

----------

Just for sure: by Policy I mean exactly this

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,811 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-02-04T03:41:24.433+00:00

    Hi @David Kříž ,

    > How to convert this "Policy result" file from XML to Excel?

    We can import and covert XML files to tables using power query. Please refer to the blog Import XML Files Into Excel to get more detail steps. The blog also have a video tutorial about this.

    If the response is helpful, please click "Accept Answer", thank you.

    0 comments
  2. David Kříž 1 Reputation point
    2021-02-04T14:50:12.53+00:00

    For "YitzhakKhabinsky-0887":
    I attached the requested file to this answer.
    64055-policy-result-2021-02-04.xml

    0 comments
  3. Yitzhak Khabinsky 25,116 Reputation points
    2021-02-04T15:27:26.637+00:00

    @David Kříž ,
    Upon looking at the attached XML file it became clear why it is in the XML format.
    It is very hierarchical, not for the MS Excel's rectangular grid style representation.
    IMHO, the XML file actual content is not for the human eyes consumption.

    I am afraid you would need to stick with the XML.

    Here is what I see in Excel for the provided XML file:

    64065-screenshot-2021-02-04-104217.png

    UPDATE
    I saved your XML document on the file system as-is. Here is a query of it via T-SQL in SSMS treating it as a virtual database table on the file system. Not sure if its result is exactly what you need. 

    WITH XMLNAMESPACES ('http://schemas.serviceml.org/smlif/2007/02' AS ns1  
 , 'http://www.w3.org/2001/XMLSchema' AS xs  
 , 'http://schemas.microsoft.com/sqlserver/DMF/2007/08' AS DMF  
 , 'http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08' AS sfc  
 , 'http://schemas.serviceml.org/sml/2007/02' AS sml)  
,rs (xmlData) AS  
(  
   SELECT TRY_CAST(BulkColumn AS XML)   
   FROM OPENROWSET(BULK N'e:\temp\policy-result-2021-02-04.xml', SINGLE_BLOB) AS x  
)  
SELECT c.value('(DMF:EvaluationDetail/DMF:TargetQueryExpression/text())[1]', 'VARCHAR(1000)') AS [Target]  
   , c.value('(DMF:EvaluationDetail/DMF:Parent/sfc:Reference/sml:Uri/text())[1]', 'VARCHAR(1000)') AS [Policy]  
   , c.value('(DMF:EvaluationDetail/DMF:Result/text())[1]', 'VARCHAR(30)') AS [Result]  
FROM rs   
 CROSS APPLY xmlData.nodes('/PolicyEvaluationResults/ns1:model/xs:bufferSchema/ns1:definitions/ns1:document/ns1:data/xs:schema/DMF:bufferData/ns1:instances/ns1:document/ns1:data') AS t(c);
  4. David Kříž 1 Reputation point
    2021-03-08T08:36:14.427+00:00

    Hi, @Yitzhak Khabinsky . Thank you for your reply. Yes, your T-SQL query is close to the desired result. :-) Unfortunately, it is still too far from the usable version. I have tried to modify your T-SQL query, but unfortunately without success. :-( In the attached file 75309-policy-based-management-results.pdf you can see the desired result.

    0 comments