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

Sign in to queue

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