CODEWITHSUNDEEP
sql-serverxml
Michael
Michael

Reputation: 2657

SQL Server output as XML all on one line

I'm using SQL Server 2014 and I'm new to XML. I'm trying to export 3 tables into XML. The 3 tables are:

Property (Parent) PropertyArea (Child) PropertyNotes (Child)

In SQL world, there is a left join from:

Property > PropertyArea Property > PropertyNotes

The primary and foreign key is [property_id]

I have the below SQL code. However, when I open it in Notepad ++ it just creates the XML all on one line, and I was expecting to see it formatted.

DECLARE @XMLOutput XML
DECLARE @XMLOutputChar nvarchar(max)

;WITH XMLNAMESPACES('MyNameSpace' as ns)

SELECT @XMLOutput =
(
SELECT ISNULL(T1.[propertyusercode],'')AS propertyusercode, 
       ISNULL(T1.[NAME],'')AS [NAME],
    (
        SELECT
        (
        SELECT 
        [AreaCode]
        ,[AreaDesc]
        FROM [PropertyArea]
        WHERE [Property_Id] = T1.property_id 
        FOR XML PATH('Area'), TYPE
        )
        FOR XML PATH('Area'), TYPE
    ),
    (
        SELECT
        (
        SELECT 
        [NotesModuleNumber]
        ,[NotesPageNumber]
        ,[NotesText]
        FROM [PropertyNotes]
        WHERE [Property_Id] = T1.property_id
        FOR XML PATH('Notes'), TYPE
        )
        FOR XML PATH('Notes'), TYPE
    )
FROM   Property T1 


    FOR XML PATH('Property'),TYPE, ROOT('Loader'),ELEMENTS XSINIL
)

SET @XMLOutputChar = '<?xml version="1.0" encoding="UTF-8"?>' + CONVERT(nvarchar(max),@XMLOutput)

SELECT @XMLOutputChar AS XMLOutput

Am I missing something? Is my code correct?

Upvotes: 1

Views: 1504

Answers (1)

EzLo
EzLo

Reputation: 14209

If you just want to see it on Notepad++, use plugin XML Tools to "pretty print" the XML into an easier to read format.

enter image description here

Copy paste from SQL Server:

enter image description here

After formatting:

enter image description here

Upvotes: 2

Related Questions