Following an XML schema, recreate XML, extract data from it and store it into an SQL database

Question

Actually here I have 2 questions, but because they are closely related I have to present them here. I have the following XML schema (part of it):

                <xs:element name="maaktDeelUitVan" minOccurs="1" maxOccurs="1">
                    <xs:complexType>
                        <xs:choice minOccurs="1" maxOccurs="unbounded">
                            <xs:element ref="PandRef"/>
                        </xs:choice>
                    </xs:complexType>
                </xs:element>

Now, based on this schema I can guess that the final XML can look like:

  <maaktDeelUitVan>
    <PandRef>1</PandRef>
    <PandRef>2</PandRef>
  </maaktDeelUitVan>

If that is so, how I can create XPath query to get PandRef as 2 rows?

I've tried this one:

SELECT
    Tab.Col.query('./PandRef').value('.', 'varchar(100)') AS heeftAlsHoofdadres
FROM   @xml.nodes('/maaktDeelUitVan') Tab(Col)

but that is returning 12, and I need 2 rows each with the values from PandRef. Maybe this is simple as it can be, but I can't figure it out.

Answer 1

Please try the following solution.

SQL

DECLARE @xml XML =
N'<maaktDeelUitVan>
    <PandRef>1</PandRef>
    <PandRef>2</PandRef>
</maaktDeelUitVan>';

SELECT c.value('(./text())[1]', 'VARCHAR(20)') AS PandRef
FROM @xml.nodes('/maaktDeelUitVan/PandRef') AS t(c);

Output

+---------+
| PandRef |
+---------+
|       1 |
|       2 |
+---------+