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.
How to convert SQL-Server "Policy result" file from XML to Excel?
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
4 answers
Sort by: Newest
-
-
Yitzhak Khabinsky 25,206 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:
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);
-
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 -
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.