Using XML in SQL Server and Azure SQL Database: (06) Creating XML from Relational Data

Sign in to queue

Description

Find out how to use the FOR XML clause to generate XML from relational data structures.

Embed

Download

Download this episode

Download captions

The Discussion

  • User profile image
    Tavis

    At around 23:15 in the video, the demonstration shows XML from the subquery being represented as escaped text (with < and > entities replacing the angle brackets).

    I think the simplest way to override this XML-as-text behaviour is to specify that you want an XML type, amending the FOR XML PATH clause in the subquery with ", TYPE":

    FOR XML PATH(''), ROOT('items'), TYPE),

    This should result in the entire XML document being produced:

    <invoice>
      <invoiceno>71774</invoiceno>
      <date>2008-06-01T00:00:00</date>
      <customer id="29847" />
      <items>
        <item productid="836" />
        <item productid="822" />
      </items>
      <totaldue>806.2326</totaldue>
    </invoice>

Add Your 2 Cents