I have a few "given" things:
- An SQL Server 2012 table with certain structure
- Data provided as
XMLwith 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)', 'NVARCHAR(100)')), (SELECT @data.value('(/Person/Surname)', 'NVARCHAR(100)')), (SELECT @data.value('(/Person/Nationality/IsoAlpha2)', '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