How to return multiple values from XML element in SQL?

Question

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?

Do you need the values in a separate column, rather than a row? If so, the only method would be with dynamic SQL. A result set must be well defined, so if you need a dynamic number of columns you need a dynamic statement. Personally I suggest against this if you have no idea what you're doing with such syntax. — Thom A
– Thom A ♦, Commented Jun 24, 2021 at 13:45
Since I have no experience using dynamic SQL I will heed your advice. If the values were in rows instead of separate columns how would you suggest doing this? — IT Noob
– IT Noob, Commented Jun 24, 2021 at 14:01

Yitzhak Khabinsky · Accepted Answer · 2021-06-24 15:00:55Z

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                                   |
+----+--------------------------------------------+

Collectives™ on Stack Overflow

How to return multiple values from XML element in SQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related