How do I select the node values from the following XML using SQL?

Question

I have the following:

DECLARE @XML XML
  SET @XML = '<?xml version="1.0" encoding="utf-8"?>
<member xmlns="http:...xsd" xmlns:xsi="http:...XMLSchema-instance">
  <Person>
    <Name>Jorge</Name>
    <LastName>Bond</LastName>
  </Person>
  <Person>
    <Name>Jorge</Name>
    <LastName>Bond</LastName>
  </Person>
</member>

I have a table with the following columns:

• Member
• Person
• Name
• LastName

How do I go about adding the values from the XML to my table?

If I'm not mistaken I'd have to do a CROSS APPLY but im not sure how to do this.

Answer 1

You can use something like this:

DECLARE @XML XML
SET @XML = '<?xml version="1.0" encoding="utf-8"?>
<member xmlns="http://testxsd" xmlns:xsi="http:...XMLSchema-instance">
  <Person>
    <Name>Jorge</Name>
    <LastName>Bond</LastName>
  </Person>
  <Person>
    <Name>Jorge</Name>
    <LastName>Bond</LastName>
  </Person>
</member>'

;WITH XMLNAMESPACES(DEFAULT 'http://testxsd')
SELECT
    PersonName = XPerson.value('(Name)[1]', 'varchar(50)'),
    PersonLastName = XPerson.value('(LastName)[1]', 'varchar(50)')
FROM @XML.nodes('/member/Person') AS XTbl(XPerson)

which will give you an output of:

And of course, if you want to insert that data into a table, you can use:

;WITH XMLNAMESPACES(DEFAULT 'http://testxsd')
INSERT INTO dbo.Person(Name, LastName)
   SELECT
       PersonName = XPerson.value('(Name)[1]', 'varchar(50)'),
       PersonLastName = XPerson.value('(LastName)[1]', 'varchar(50)')
   FROM @XML.nodes('/member/Person') AS XTbl(XPerson)