XQuery with dynamic XPath from XML Sql Server Column

Question

I have a log table has xml column which contains log contents

There is also table called logType which is the type of log

I need to create query descripes the xml contents as readable string

I added a column with name logXPath to logtype table

and i created the following query

SELECT   contents.value(LogXPath, 'nvarchar(max)')
FROM    dbo.Log 
        JOIN dbo.LogType ON dbo.Log.logTypeID = dbo.LogType.logTypeID

and I got the following error

The argument 1 of the XML data type method "value" must be a string literal

and I searched for a way to do this with no results!!

Is there any do dynamic xpath in Sql Server XML Column?

Edit

for example assume the following schema and data

CREATE TABLE [dbo].[logType]
    (
      [logTypeID] [int] NOT NULL ,
      [logTypeName] [nvarchar](50) NOT NULL ,
      [xPath] [nvarchar](MAX) NOT NULL ,
      CONSTRAINT [PK_logType] PRIMARY KEY CLUSTERED ( [logTypeID] ASC )
    )

GO
INSERT  [dbo].[logType]
        ( [logTypeID] ,
          [logTypeName] ,
          [xPath]
        )
VALUES  ( 1 ,
          N'Patient Data' ,
          N'(/Patient/PatientName)[1]'
        )
INSERT  [dbo].[logType]
        ( [logTypeID] ,
          [logTypeName] ,
          [xPath]
        )
VALUES  ( 2 ,
          N'Clinic Data' ,
          N'(/Clinic/ClinicName)[1]'
        )
/****** Object:  Table [dbo].[log]    Script Date: 02/04/2015 13:58:47 ******/
GO
CREATE TABLE [dbo].[log]
    (
      [logID] [int] NOT NULL ,
      [logTypeID] [int] NOT NULL ,
      [Contents] [xml] NULL ,
      CONSTRAINT [PK_log] PRIMARY KEY CLUSTERED ( [logID] ASC )
    )
GO
INSERT  [dbo].[log]
        ( [logID] ,
          [logTypeID] ,
          [Contents]
        )
VALUES  ( 1 ,
          1 ,
          N'<Patient><PatientID>1</PatientID><PatientName>john</PatientName></Patient>'
        )
INSERT  [dbo].[log]
        ( [logID] ,
          [logTypeID] ,
          [Contents]
        )
VALUES  ( 2 ,
          2 ,
          N'<Clinic><ClinicID>1</ClinicID><ClinicName>Clinic 1</ClinicName></Clinic>'
        )

When I make query like the following ,it gives me the error

SELECT  logTypeName ,
        [Contents].value(dbo.logType.xPath, 'nvarchar(max)') AS data
FROM    dbo.[log]
        JOIN dbo.logType ON dbo.[log].logTypeID = dbo.logType.logTypeID

Answer 1

Okay, so this has been here a while (a year), but this might be helpful...

A VERY helpful TABLE function: http://beyondrelational.com/modules/2/blogs/28/posts/10495/xquery-lab-58-select-from-xml.aspx

Using that function, you can get the values you're after something like this:

Select l.Contents, t.XPath, x.Value
  From [log] l With (NoLock)
 Inner Join [LogType] t With (NoLock)
         on t.LogTypeID=l.LogTypeID
 CROSS APPLY XMLTable(l.Contents) AS x
 Where REPLACE(REPLACE(REPLACE(t.XPath,'[1]',''),'(',''),')','')=REPLACE('/'+x.XPath,'[1]','')

Answer 2

You can build a query dynamically using the table LogType.

declare @SQL nvarchar(max);

set @SQL = 'select case L.logTypeID'+
            (
            select ' when '+cast(LT.logTypeID as varchar(11))+
                     ' then L.Contents.value('''+LT.xPath+''', ''nvarchar(max)'')'
            from LogType as LT
            for xml path('')
            )+' end as Name from dbo.[Log] as L;';

exec (@SQL);

It will give you a query that looks like this:

select case L.logTypeID 
         when 1 then L.Contents.value('(/Patient/PatientName)[1]', 'nvarchar(max)')
         when 2 then L.Contents.value('(/Clinic/ClinicName)[1]', 'nvarchar(max)') 
       end as Name 
from dbo.[Log] as L;

Answer 3

SQL Server does not allow replacing entire XPath expression with a variable, but you can use sql:variable and sql:column extensions inside the expression (I can't say how exactly without seeing your xml structure and what information you want to query from XML column).

Or, as mentioned above, you can use dynamic SQL:

DECLARE @xpath NVARCHAR(MAX);
SET @xpath = ... //Calculate xpath expression here

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT contents.value(''' + @xpath + ''', ''NVARCHAR(MAX)''
             FROM dbo.Log 
             JOIN dbo.LogType ON dbo.Log.logTypeID = dbo.LogType.logTypeID';

EXEC sp_executesql @sql;