Shredding XML into an SQL table

Question

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 = '<Person>
 <FirstName>John</FirstName>
 <Surname>Doe</Surname>
 <Nationality>
 <Fullname>Netherlands</Fullname>
 <IsoAlpha2>NL</IsoAlpha2>
 </Nationality>
</Person>';
-- 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?

Answer 1

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