Use of Interactive SQL to view results

The result of a FOR XML query is returned as a string. In many cases, the string result can be quite long. Interactive SQL includes the ability to display the structure of a well-formed XML document using the View in Window option.

The result of a FOR XML query can be cast into a well-formed XML document with the inclusion of an <?xml?> tag and an arbitrary enclosing pair of tags (for example, <root>...</root>). The following query illustrates how to do this.

SELECT XMLCONCAT( CAST('<?xml version="1.0"?>' AS XML), 
  XMLELEMENT( NAME root, (
    SELECT
          1          AS tag,
          NULL       AS parent,
          EmployeeID AS [employee!1!employeeID],
          NULL       AS [customer!2!customerID],
          NULL       AS [customer!2!region],
          NULL       AS [custname!3!given_name!element],
          NULL       AS [custname!3!surname!element]
    FROM Employees
    UNION DISTINCT 
    SELECT
          2,
          1,
          EmployeeID,
          CustomerID,
          Region,
          NULL,
          NULL
    FROM Employees KEY JOIN SalesOrders
    UNION DISTINCT 
    SELECT
          3,
          2,
          EmployeeID,
          CustomerID,
          NULL,
          Customers.GivenName,
          Customers.SurName
    FROM SalesOrders 
    JOIN Customers 
        ON SalesOrders.CustomerID = Customers.ID 
    JOIN Employees 
        ON SalesOrders.SalesRepresentative = Employees.EmployeeID 
    ORDER BY 3, 4, 1
    FOR XML EXPLICIT
  ) ) 
);

The Interactive SQL column Truncation length value must be set large enough to fetch the entire column. This can be done using the Tools » Options menu or by executing an Interactive SQL statement like the following.

SET OPTION truncation_length = 80000;

To view the XML document result, double-click the column contents in the Results pane and select the XML Outline tab.

Use of Interactive SQL to view results

See also