2
\$\begingroup\$

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?

\$\endgroup\$
4
+50
\$\begingroup\$
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

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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