Selecting XML Data from SQL query - No values returned

Question

Xml Column returns no rows.
Below created a table type and xml value inserted into that table. After executing the below mentioned two queries no value is returned.
Xml data is a valid data.

DECLARE @Test TABLE (Id INT IDENTITY (1,1), XMLDATA XML)

INSERT INTO @test
SELECT '
<TXLife xmlns="http://ACORD.org/Standards/Life/2" xmlns:tx="http://ACORD.org/Standards/Life/2" Version="2.20.00">
    <TXLifeRequest PrimaryObjectID="Holding_1">
        <CorrelationGUID>4b30545a-158b-441a-a37a-0b259f757059</CorrelationGUID>
    </TXLifeRequest>
</TXLife>'

SELECT 
      Id
    , XMLDATA.query('//CorrelationGUID') AS 'TransType'
    , XMLDATA
FROM @test

SELECT C.value('./CorrelationGUID[1]', 'varchar(50)') AS 'TransType'
FROM @test
CROSS APPLY XMLDATA.nodes('/TXLife/TXLifeRequest') n (C)

Community · Accepted Answer · 2017-05-23 10:32:15Z

1

Well, if I test your code with CREATE TABLE, here and here, the unfiltered select works just fine. However, the second filtered select returns no results.

If I alter the query to to specify the right namespace, like in @Devart's answer,

WITH XMLNAMESPACES (DEFAULT 'http://ACORD.org/Standards/Life/2')
SELECT
        C.value('./CorrelationGUID[1]', 'varchar(50)') AS 'TransType'
    FROM
        Test
    CROSS APPLY
        XMLDATA.nodes('/TXLife/TXLifeRequest') n (C)

You can see, it works as expected.

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Aug 27, 2013 at 9:01

Jodrell

35.8k6 gold badges94 silver badges131 bronze badges

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

1 Comment

Alex Filipovici Over a year ago

TransType is empty.

Alex Filipovici · Accepted Answer · 2013-08-27 09:32:39Z

1

You need to declare the namespace. Try this:

SELECT
  XMLDATA.value('declare namespace L="http://ACORD.org/Standards/Life/2";(//L:CorrelationGUID)[1]',
                'UNIQUEIDENTIFIER') AS 'TransType'
FROM   @test

UNION ALL

SELECT
  C.value('declare namespace L="http://ACORD.org/Standards/Life/2";(./L:CorrelationGUID)[1]',
          'UNIQUEIDENTIFIER') AS 'TransType'
FROM   @test
CROSS APPLY
  XMLDATA.nodes('declare namespace L="http://ACORD.org/Standards/Life/2";/L:TXLife/L:TXLifeRequest') N(C)

See it working here.

edited Aug 27, 2013 at 9:32

answered Aug 27, 2013 at 9:23

Alex Filipovici

32.8k7 gold badges60 silver badges81 bronze badges

Comments

Devart · Accepted Answer · 2013-08-27 09:01:04Z

0

Try this one -

DECLARE @XML XML
SELECT  @XML = '
<TXLife xmlns="http://ACORD.org/Standards/Life/2" xmlns:tx="http://ACORD.org/Standards/Life/2" Version="2.20.00">
    <TXLifeRequest PrimaryObjectID="Holding_1">
        <CorrelationGUID>4b30545a-158b-441a-a37a-0b259f757059</CorrelationGUID>
    </TXLifeRequest>
</TXLife>'

;WITH XMLNAMESPACES (DEFAULT 'http://ACORD.org/Standards/Life/2')
SELECT t.c.value('CorrelationGUID[1]', 'UNIQUEIDENTIFIER')
FROM @XML.nodes('//TXLifeRequest') t(c)

Output -

------------------------------------
4B30545A-158B-441A-A37A-0B259F757059

edited Aug 27, 2013 at 9:01

answered Aug 27, 2013 at 8:55

Devart

120k23 gold badges172 silver badges189 bronze badges

Collectives™ on Stack Overflow

Selecting XML Data from SQL query - No values returned

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related