question

DavidK-6357 avatar image
0 Votes"
DavidK-6357 asked ·

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


sql-server-general
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Please edit your question and add the XML file.

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered ·

Hi @DavidK-6357,

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.



·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DavidK-6357 avatar image
0 Votes"
DavidK-6357 answered ·

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



·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered ·

@DavidK-6357,
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);


· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your reply. But as you can see for example here ( policyfield7.png ) Management-Studio is able to show it in table format. Because probably I don't need all information stored in XML format. For me only name of MS-SQL-Server + Policy + Result (OK/Error) + Condition is enough.


0 Votes 0 ·

@DavidK-6357,

I updated the answer. Check it out.

0 Votes 0 ·
DavidK-6357 avatar image
0 Votes"
DavidK-6357 answered ·

Hi, @YitzhakKhabinsky-0887 . 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.




·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.