Reputation: 2657
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
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.
Copy paste from SQL Server:
After formatting:
Upvotes: 2