In the previous part, we learned how to use the default modes and how we can change the shapes of the XML model using the same available modes. We will learn how to read the XML file that is generated by SQL Server.
The code shown in the screenshot below returns a different mode than the actual modes that
are available.
Once the query is executed, the results are shown in the result pane, which can be saved as a CSV file by right clicking on the result, but it is not the best way to save the SQL Server-generated XML files since the data can be truncated due to the size configured in the SQL Server.
Click on the result from the result-pane. The entire XML document gets opened in a new window. Go to File and select “Save As”. It will prompt you the file-directory to save the file based on your choice.
Once the file is saved, open a new query window and type the below command –
SELECT CAST(XMLData AS XML)
FROM OPENROWSET(
BULK ‘D:\MyDocuments\blog posts\XML-SQLServer\test.xml’
, SINGLE_BLOB) AS X(XMLData)
Please do let me know if you need more clarification.
Big Data and SQL 
One comment