Parse XML to SQL with attributes

XmlToSqlWithAttributes.sql

declare @NotesXml xml = 
'<root>
    <Notes CreationDate="01/18/2015" Note="Note 1" UserID="10"/>
    <Notes CreationDate="01/18/2015" Note="Note 2" UserID="20"/>
</root>'
 
declare @notesTable as table (
    CreationDate datetime,
    Note         varchar(max),
    UserID       int
)
 
INSERT INTO @notesTable (CreationDate
                          ,Note
                          ,UserID)
SELECT   col.value('(@CreationDate)[1]',  'datetime')     AS CreationDate
        ,col.value('(@Note)[1]',          'varchar(max)') AS Note
        ,col.value('(@UserID)[1]',        'int')          AS UserID
FROM @NotesXml.nodes('//Notes') tab(col)

select * from @notesTable

/* Output:

CreationDate            Note                 UserID
----------------------- -------------------- -----------
2015-01-18 00:00:00.000 Note 1               10
2015-01-18 00:00:00.000 Note 2               20

*/

If Notes is empty we'll get one record with null values

declare @NotesXml xml = 
'<root>
    <Notes />
</root>'

declare @notesTable as table (
    CreationDate datetime,
    Note         varchar(max),
    UserID       int
)

INSERT INTO @notesTable (CreationDate
                          ,Note
                          ,UserID)
SELECT   col.value('(@CreationDate)[1]',  'datetime')     AS CreationDate
        ,col.value('(@Note)[1]',          'varchar(max)') AS Note
        ,col.value('(@UserID)[1]',        'int')          AS UserID
FROM @NotesXml.nodes('//Notes') tab(col)

select * from @notesTable

/*
CreationDate            Note     UserID
----------------------- -------- -----------
NULL                    NULL     NULL
*/