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

How about executing the whole `SELECT` statement as dynamic SQL? — Code Different, Feb 03 '15 at 14:55
the problem is my xpath can't be a variable it stored in sql column and has different value for each logtype and the xml structure is different for each logtype so if I want to use dynamic sql i have to create a query for each logtype but i need to get then in one query — user897645, Feb 03 '15 at 15:42

score 1 · Answer 1 · answered Feb 04 '15 at 13:14

1

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;

answered Feb 04 '15 at 13:14

Mikael Eriksson

136,425
22
210
281

good solution for small numbers of types, but logtype it self contains about 1000+ record – user897645 Feb 04 '15 at 13:19
@user897645 Yes, that could be a problem. I reached the limit on my computer at 675 different types. After that SQL Server could not create a query plan. – Mikael Eriksson Feb 04 '15 at 13:34
@user897645 Is the shape of the xPath always the same? One root and get the first value of a child node to root? – Mikael Eriksson Feb 04 '15 at 13:35
For the shapes in your sample you can just use `select L.Contents.value('(/*/*)[2]', 'nvarchar(max)') from dbo.[log] as L;` – Mikael Eriksson Feb 04 '15 at 13:37

score 1 · Answer 2 · answered Mar 30 '16 at 16:28

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]','')

score 0 · Answer 3 · answered Feb 03 '15 at 15:32

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;

the problem is my xpath can't be a variable it stored in sql column and has different value for each logtype and the xml structure is different for each logtype — user897645, Feb 03 '15 at 15:38

XQuery with dynamic XPath from XML Sql Server Column

3 Answers3