How to return multiple values from XML element in SQL?

I need to pull information from the “Name” element from an XML column in SQL. An example of the XML is below:

<ArrayOfTarget xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/TriTech.InformRMS.Domain.Core.ComplexTypes">
  <Target>
    <AgencyId>ca2fa1dd-2cd4-c219-bea5-08d6fbe6d96c</AgencyId>
    <AgencyName i:nil="true" />
    <Id>19bc33e1-a788-cd99-3dab-08d92aa7d030</Id>
    <Name>Case Number WB21-006637</Name>
    <Type>Case Management</Type>
  </Target>
  <Target>
    <AgencyId>ca2fa1dd-2cd4-c219-bea5-08d6fbe6d96c</AgencyId>
    <AgencyName i:nil="true" />
    <Id>cb4d829b-c31a-cadb-5c9e-08d934b7404d</Id>
    <Name>Incident Supplement Number WB21-006637.006</Name>
    <Type>Data Entry</Type>
  </Target>
  <Target>
    <AgencyId>ca2fa1dd-2cd4-c219-bea5-08d6fbe6d96c</AgencyId>
    <AgencyName i:nil="true" />
    <Id>6b23195c-4650-c0c9-925d-08d71a88f611</Id>
    <Name i:nil="true" />
    <Type>Template</Type>
  </Target>
  <Target>
    <AgencyId>ca2fa1dd-2cd4-c219-bea5-08d6fbe6d96c</AgencyId>
    <AgencyName i:nil="true" />
    <Id>b465517c-5926-c6b3-1cc6-08d6fbe6da27</Id>
    <Name>Default Workflow</Name>
    <Type>Workflow</Type>
  </Target>
  <Target>
    <AgencyId i:nil="true" />
    <AgencyName i:nil="true" />
    <Id i:nil="true" />
    <Name>Complete</Name>
    <Type>Workflow Step</Type>
  </Target>
</ArrayOfTarget>

I have this SQL Query which works for returning five of the “Name” elements:

;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' AS i,
                    'http://schemas.datacontract.org/2004/07/TriTech.InformRMS.Domain.Core.ComplexTypes' AS s)
SELECT TOP 100
TargetData_Xml,
Description,
TargetData_Xml.value('(/s:ArrayOfTarget/s:Target/s:Name)[1]', 'varchar(100)') as Context1,
TargetData_Xml.value('(/s:ArrayOfTarget/s:Target/s:Name)[2]', 'varchar(100)') as Context2,
TargetData_Xml.value('(/s:ArrayOfTarget/s:Target/s:Name)[3]', 'varchar(100)') as Context3,
TargetData_Xml.value('(/s:ArrayOfTarget/s:Target/s:Name)[4]', 'varchar(100)') as Context4,
TargetData_Xml.value('(/s:ArrayOfTarget/s:Target/s:Name)[5]', 'varchar(100)') as Context5
FROM
[InformRMSAudit].[dbo].[AuditEntities]
WHERE
CaseNumber = 'RP21-010802'
ORDER BY
Date desc

That would be sufficient if every record had only five “Name” elements in the XML, but the number of “Name” elements varies from record to record.

How could I change my query to handle the variation from record to record?

Answer

Please try the following solution.

As @Larnu pointed out, it is much better to shred the XML as rows.

If needed it is very easy to filter out names with NULL values.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, TargetData_Xml XML);
INSERT INTO @tbl (TargetData_Xml) VALUES
(N'<ArrayOfTarget xmlns:i="http://www.w3.org/2001/XMLSchema-instance"
               xmlns="http://schemas.datacontract.org/2004/07/TriTech.InformRMS.Domain.Core.ComplexTypes">
    <Target>
        <AgencyId>ca2fa1dd-2cd4-c219-bea5-08d6fbe6d96c</AgencyId>
        <AgencyName i:nil="true"/>
        <Id>19bc33e1-a788-cd99-3dab-08d92aa7d030</Id>
        <Name>Case Number WB21-006637</Name>
        <Type>Case Management</Type>
    </Target>
    <Target>
        <AgencyId>ca2fa1dd-2cd4-c219-bea5-08d6fbe6d96c</AgencyId>
        <AgencyName i:nil="true"/>
        <Id>cb4d829b-c31a-cadb-5c9e-08d934b7404d</Id>
        <Name>Incident Supplement Number WB21-006637.006</Name>
        <Type>Data Entry</Type>
    </Target>
    <Target>
        <AgencyId>ca2fa1dd-2cd4-c219-bea5-08d6fbe6d96c</AgencyId>
        <AgencyName i:nil="true"/>
        <Id>6b23195c-4650-c0c9-925d-08d71a88f611</Id>
        <Name i:nil="true"/>
        <Type>Template</Type>
    </Target>
    <Target>
        <AgencyId>ca2fa1dd-2cd4-c219-bea5-08d6fbe6d96c</AgencyId>
        <AgencyName i:nil="true"/>
        <Id>b465517c-5926-c6b3-1cc6-08d6fbe6da27</Id>
        <Name>Default Workflow</Name>
        <Type>Workflow</Type>
    </Target>
    <Target>
        <AgencyId i:nil="true"/>
        <AgencyName i:nil="true"/>
        <Id i:nil="true"/>
        <Name>Complete</Name>
        <Type>Workflow Step</Type>
    </Target>
</ArrayOfTarget>');
-- DDL and sample data population, end

WITH XMLNAMESPACES (DEFAULT 'http://schemas.datacontract.org/2004/07/TriTech.InformRMS.Domain.Core.ComplexTypes')
SELECT ID
    , c.value('(Name/text())[1]', 'VARCHAR(100)') AS [Name]
FROM @tbl
    CROSS APPLY TargetData_Xml.nodes('/ArrayOfTarget/Target') AS t(c);

Output

+----+--------------------------------------------+
| ID |                    Name                    |
+----+--------------------------------------------+
|  1 | Case Number WB21-006637                    |
|  1 | Incident Supplement Number WB21-006637.006 |
|  1 | NULL                                       |
|  1 | Default Workflow                           |
|  1 | Complete                                   |
+----+--------------------------------------------+