XML in SQL Server - Part-2

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.

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData

FOR XML RAW;

Once you execute your query, you will see the results in the result-pane, which can be saved as CSV file, however, it shouldn’t be the right way to save XML files that generated by 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.