CODEWITHSUNDEEP
sqlsql-serverxml
Andreas Reiff
Andreas Reiff

Reputation: 8404

Get or select value out of xml column in SQL (Server) - remote function reference not allowed

I want to filter rows based on certain values inside an xml field (Data Type xml) in that row.

I tried both explicitly selecting that value (commented out) or filtering on that value (as given below),

SELECT TOP 1000 [Guid]
--  ,dbo.mytable.myxml.value('(/Planning/Party)[1]', 'varchar(max)') as PARTY

  FROM [MY_DB].[dbo].[mytable]
  WHERE dbo.mytable.myxml.value('(/Planning/Party)[1]', 'varchar(max)') like 'TRUE'

but am getting the same error message

Remote function reference 'dbo.mytable.myxml.value' is not allowed, and the column name 'dbo' could not be found or is ambiguous.

This is working:

SELECT TOP 1000 [Guid]
    ,dbo.mytable.myxml    
  FROM [MY_DB].[dbo].[mytable]

Is there any way to do what I am trying? I copied the syntax from samples out of other answers here, and found no mention of the problem I am having.

I am testing the function in SSMS with SQL Server 2014, later on to embed it into code.

Update - this is working:

SELECT TOP 1000 [Guid]
    ,dbo.mytable.myotherval1
    ,dbo.mytable.myotherval2
    ,mytable.myxml.value('(/Planning/Party)[1]', 'varchar(max)') as PARTY    
  FROM [MY_DB].[dbo].[mytable]

Upvotes: 0

Views: 780

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

It might be connected to the qualifier FROM MY_DB.dbo.mytable, while in the latter you are using the same without MY_DB..

I think, that the currently active db, which you are targeting with a simple dbo. has not got the same objects in this context.

Try something like this (with or without MY_DB):

SELECT TOP 1000 [Guid]
               ,t.myxml.query('.') as TestXml
FROM [MY_DB].[dbo].[mytable] AS t;

If this works, the next should work too: 

SELECT TOP 1000 [Guid]
               ,t.myxml.value('(/Planning/Party)[1]', 'varchar(max)') as PARTY
FROM [MY_DB].[dbo].[mytable] AS t
WHERE t.myxml.value('(/Planning/Party)[1]', 'varchar(max)') like 'TRUE';

There might be better approaches... Might help to show a (reduced!) example of your XML.

Upvotes: 1

Related Questions