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.

The code shown in the screenshot below returns different mode than the actual modes that
are available.

xml_2_invalidformat

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.

xml_2_saveAs

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)

xml_2_readitinsql

Please do let me know if you need more clarification.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s