CODEWITHSUNDEEP
sqlsql-serverxmlxquery
IT Noob
IT Noob

Reputation: 15

How to get data from XML column in SQL Server?

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]

Upvotes: 0

Views: 655

Answers (1)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22321

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

Upvotes: 1

Related Questions