How to get data from XML column in SQL Server?

Question

I have a XML column in a SQL Server database. I want to get results from that column. Here is what the XML column looks like:

<ArrayOfTarget xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/TriTech.InformRMS.Domain.Core.ComplexTypes">
  <Target>
    <AgencyId i:nil="true" />
    <AgencyName i:nil="true" />
    <Id i:nil="true" />
    <Name>Quick Search</Name>
    <Type>Search</Type>
  </Target>
  <Target>
    <AgencyId i:nil="true" />
    <AgencyName i:nil="true" />
    <Id i:nil="true" />
    <Name>Quick Search = wbinc2000125</Name>
    <Type>Quick Search</Type>
  </Target>
  <Target>
    <AgencyId i:nil="true" />
    <AgencyName i:nil="true" />
    <Id i:nil="true" />
    <Name>Results (0)</Name>
    <Type>Result</Type>
  </Target>
</ArrayOfTarget>

Here are some things I have tried but no results:

select 
    [XML].value ('(ArrayofTarget/Target/Name/node())[1]', 'nvarchar(max)') as Results
from 
    [DB].[dbo].[Table]

Another example:

;WITH XMLNAMESPACES (N'http://www.w3.org/2001/XMLSchema-instance' as X)
SELECT
    [XML].value('(/X:ArrayOfTarget/X:Target/X:Name[1]', 'nvarchar(max)') as Name
FROM [DB].[dbo].[Table]

You're on the right track with WITH XMLNAMESPACES, but the relevant namespace is the default namespace mentioned in xmlns (http://schemas.datacontract.org/2004/07/TriTech.InformRMS.Domain.Core.ComplexTypes). You can use DEFAULT for that. — Jeroen Mostert
– Jeroen Mostert, Commented Apr 20, 2021 at 15:00

Yitzhak Khabinsky · Accepted Answer · 2021-04-20 15:23:36Z

1

Here is a working example.

It shows how to handle properly a default namespace as well as how to use two XQuery methods: .nodes() and .value()

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) 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 i:nil="true"/>
        <AgencyName i:nil="true"/>
        <Id i:nil="true"/>
        <Name>Quick Search</Name>
        <Type>Search</Type>
    </Target>
    <Target>
        <AgencyId i:nil="true"/>
        <AgencyName i:nil="true"/>
        <Id i:nil="true"/>
        <Name>Quick Search = wbinc2000125</Name>
        <Type>Quick Search</Type>
    </Target>
    <Target>
        <AgencyId i:nil="true"/>
        <AgencyName i:nil="true"/>
        <Id i:nil="true"/>
        <Name>Results (0)</Name>
        <Type>Result</Type>
    </Target>
</ArrayOfTarget>');
-- DDL and sample data population, end

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

Output

+-----------------------------+--------------+
|            Name             |     Type     |
+-----------------------------+--------------+
| Quick Search                | Search       |
| Quick Search = wbinc2000125 | Quick Search |
| Results (0)                 | Result       |
+-----------------------------+--------------+

edited Apr 20, 2021 at 15:23

answered Apr 20, 2021 at 15:15

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

IT Noob Over a year ago

I have many different XML results to pull from that one column. Would this example work with if the XML structure is the same in each cell but has different information?

Yitzhak Khabinsky Over a year ago

@ITNoob, it should work. The dependency is on the XPath expressions only. As long as they are the same it will work.

Yitzhak Khabinsky Over a year ago

@ITNoob, if the proposed solution is working for you, please don't forget to mark it as Answer.

Collectives™ on Stack Overflow

How to get data from XML column in SQL Server?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related