11. for xml

W tym zapytaniu użyłem funkcji CONVERT(NVARCHAR(MAX), ...), aby rzutować kolumny device_location, device_location_geom i device_hierarchy na typ NVARCHAR, który jest obsługiwany w zapytaniach FOR XML.

SELECT
    device_id AS [Device/@ID],
    device_status AS [Device/Status],
    device_power AS [Device/Power],
    company_id AS [Device/CompanyID],
    CONVERT(NVARCHAR(MAX), device_location) AS [Device/Location],
    CONVERT(NVARCHAR(MAX), device_location_geom) AS [Device/LocationGeom],
    CONVERT(NVARCHAR(MAX), device_hierarchy) AS [Device/Hierarchy],
    device_description AS [Device/Description]
FROM
    Renewable_Energy_Devices
FOR XML PATH('Device'), ROOT('Devices');