Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
    
Did the answer below help you? Bounty expires today. – RubberDuck Jan 29 at 14:55
    
@RubberDuck Yes. That is, I've checked that the answer works, and feel the method is an improvement. The fact that the community did not seem to vote at all does make that I'm unsure if it's a good approach (if I did I wouldn't be asking this question :D), but I'm also guessing it's because of the lack of views, your bounty notwithstanding. – Jeroen Jan 29 at 15:26
    
Once I get back to working on my actual SQL that prompted my question I'll try to incorporate the answer's approach, and if succesful also mark the question as accepted. Until then I've upvoted it, and since there are no other answers I'd say it's fair the PO of that answer deserved the bounty. - TY once more for the altruistic action ;) – Jeroen Jan 29 at 15:28
    
Youre welcome! Good luck! – RubberDuck Jan 29 at 16:11
up vote 2 down vote accepted
+50
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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.