Shredding XML into an SQL table I have a few "given" things:


An SQL Server 2012 table with certain structure
Data provided as XML with certain structure


My code has to shred that XML into a row in the table. I'm looking to get feedback on how I'm getting the data for each column from the XML variable.

Here's a realistic yet minimal repro of my scenario:



-- Table structure is "a given", i.e. I *have to* work with this:
CREATE TABLE #Person 
(
    Id INT IDENTITY(1,1), 
    First_Name NVARCHAR(100), 
    Last_Name NVARCHAR(100), 
    Two_Char_Nationality VARCHAR(2),
    InsertedAt DATETIME -- My code has to manually insert the timestamp here (cannot create a default constraint)
);

-- Xml structure is "a given", i.e. I *have to* work with this:
DECLARE @data XML = '&lt;Person&gt;
  &lt;FirstName&gt;John&lt;/FirstName&gt;
  &lt;Surname&gt;Doe&lt;/Surname&gt;
  &lt;Nationality&gt;
    &lt;Fullname&gt;Netherlands&lt;/Fullname&gt;
    &lt;IsoAlpha2&gt;NL&lt;/IsoAlpha2&gt;
  &lt;/Nationality&gt;
&lt;/Person&gt;';

-- Looking to get feedback on this:
INSERT INTO #Person
(
    First_Name,
    Last_Name, 
    Two_Char_Nationality,
    InsertedAt
)
VALUES
(
    (SELECT @data.value('(/Person/FirstName)[1]', 'NVARCHAR(100)')),
    (SELECT @data.value('(/Person/Surname)[1]', 'NVARCHAR(100)')),
    (SELECT @data.value('(/Person/Nationality/IsoAlpha2)[1]', 'VARCHAR(2)')),
    GETDATE()
);

-- For testing purposes only:
SELECT * FROM #Person
DROP TABLE #Person


As you can see I'm doing many sub-select-queries in the VALUES(...) part, which doesn't feel all too smart.

In reality, there's about 10 - 20 columns in my #Person table, and sometimes my XML is slightly more nested, but I'm trying to focus on the basics first.

Any recommendations on the INSERT statement?
INSERT INTO #Person
(
    First_Name,
    Last_Name, 
    Two_Char_Nationality,
    InsertedAt
)
SELECT
    tab.p.value('FirstName[1]', 'NVARCHAR(100)'),
    tab.p.value('Surname[1]', 'NVARCHAR(100)'),
    tab.p.value('(Nationality/IsoAlpha2)[1]', 'VARCHAR(2)'),
    GETDATE()
FROM
    @data.nodes('/Person') tab(p);


SELECT or Query nodes in hierarchial or nested XML