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?