Exporting XML From MS SQL Server

Summary

This is going to be a short post on how to query an MS SQL Server database and produce XML output.

The Query

There are two formats that you can use when you want to convert a query result into XML, Elements format and Properties Format. The following query will select from a table named “product” and put the result set into elements:

SELECT * FROM product FOR XML RAW ('product'), ROOT('data'), ELEMENTS

The results window will look something like this:

Click on the url link and the output should look like this:

<data>
  <product>
    <ProductId>1</ProductId>
    <ProductType>1</ProductType>
    <Name>Matchbox Car</Name>
    <store>1</store>
  </product>
  <product>
    <ProductId>2</ProductId>
    <ProductType>1</ProductType>
    <Name>Baby Rattle</Name>
    <store>1</store>
  </product>
  <product>
    <ProductId>3</ProductId>
    <ProductType>1</ProductType>
    <Name>Lego Bricks</Name>
    <store>1</store>
  </product>
  <product>
    <ProductId>4</ProductId>
    <ProductType>2</ProductType>
    <Name>Steak</Name>
    <store>1</store>
  </product>
</data>

If you want to flatten out your XML, you can use this syntax:

SELECT * FROM product FOR XML RAW ('product')

Which will produce this output:

<product ProductId="1" ProductType="1" Name="Matchbox Car" store="1" />
<product ProductId="2" ProductType="1" Name="Baby Rattle" store="1" />
<product ProductId="3" ProductType="1" Name="Lego Bricks" store="1" />
<product ProductId="4" ProductType="2" Name="Steak" store="1" />

As you can see by the last example, you can leave the “Root” keyword off the query if you don’t want to the root data tags added.

Producing JSON

If you need to produce JSON, you will need to do it using another tool.  Use the For XML RAW command above, including the root tag (the output must be well-formed.  Then go to this site (or any other free xml to json converter website): thomasfrank.se XML to JSON – a converter

Paste your xml into the window and hit the convert button.