SQL Server can pull values out of an XML document and hand them back as ordinary rows and columns. The syntax depends on whether your data lives in elements or in attributes, and the difference is small enough that it is easy to get wrong.

Here is the same set of data both ways, starting with elements:

DECLARE @xml XML = '
<dinosaurs>
  <d>
    <name>Aachenosaurus</name>
    <url>https://en.wikipedia.org/wiki/Aachenosaurus</url>
  </d>
  <d>
    <name>Aardonyx</name>
    <url>https://en.wikipedia.org/wiki/Aardonyx</url>
  </d>
</dinosaurs>';

;WITH ShreddedData (name, url) AS
(
    SELECT x.node.value('name[1]', 'VARCHAR(255)'),
           x.node.value('url[1]',  'VARCHAR(255)')
    FROM   @xml.nodes('dinosaurs/d') x(node)
)
SELECT * FROM ShreddedData;

And the same data expressed as attributes instead:

DECLARE @xml XML = '
<dinosaurs>
  <d name="Aachenosaurus" url="https://en.wikipedia.org/wiki/Aachenosaurus" />
  <d name="Aardonyx"      url="https://en.wikipedia.org/wiki/Aardonyx" />
</dinosaurs>';

;WITH ShreddedData (name, url) AS
(
    SELECT x.node.value('@name', 'VARCHAR(255)'),
           x.node.value('@url',  'VARCHAR(255)')
    FROM   @xml.nodes('dinosaurs/d') x(node)
)
SELECT * FROM ShreddedData;

Two things are worth knowing here. The nodes() method is what turns the XML into a rowset, one row per match on the path you give it, and value() is what pulls a single scalar out of each of those rows.

The difference between the two versions is entirely in the value() argument. An attribute is addressed with an @ in front of its name. An element needs the positional predicate, name[1] rather than just name, because value() requires an expression that returns exactly one node and XPath cannot assume there is only one child with that name. Leave off the [1] and SQL Server will reject the query rather than guess.

If your XML mixes both, which real-world documents usually do, you can pull elements and attributes in the same SELECT. They are just different expressions against the same node.