Query table using values in XML column

Question

I have an XML column in my table and I want to use a value from the XML to filter the rows in my table.

Let table name be: mytable & Let column name be: mycolumn

Sample XML:

<TxMsg>
  <TxCode>sometxt</TxCode>
  <TxType>sometxttype</TxType>
  <Roads>someroads</Roads>
  <VehicleId>QWE123</VehicleId>
...etc
<TxMsg>

How should I query my table such that I can only see rows that contains VehicleId = 'QWE123'?

I am hoping to find solutions like:

SELECT * FROM mytable WHERE mycolumn.(xpath) = 'QWE123'

Mikael Eriksson · Accepted Answer · 2017-11-21 06:48:13Z

1

You should use exist() in the where clause.

select *
from dbo.mytable as T
where T.mycolumn.exist('/TxMsg/VehicleId/text()[. = "QWE123"]') = 1;

Use sql:variable if you would like to use a variable or a parameter.

declare @VehicleId varchar(6) = 'QWE123';

select *
from dbo.mytable as T
where T.mycolumn.exist('/TxMsg/VehicleId/text()[. = sql:variable("@VehicleId")]') = 1;

answered Nov 21, 2017 at 6:48

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

Comments

Katherine Elizabeth Lightsey · Accepted Answer · 2017-11-20 21:21:25Z

There's a complete prototype of how to do that in my github repository (https://github.com/KELightsey/chamomile/blob/master/presentation/xquery_shredding_a_table).

You'll want to look at that whole example, but the "filter" is based on a cross apply from the XML column like this:

{
--
-- get only the log entries having a data node in any position (//* syntax)
-------------------------------------------------
SELECT *
FROM   @entry AS [entry]
       CROSS APPLY [entry].[nodes]('/*') AS [table] ( [column] )
WHERE  CAST([table].[column].[query]('fn:local-name(.)') AS [SYSNAME]) = N'log'
       AND [entry].exist('//*[local-name()="data"]') = 1; 
}

Of course you'll want to use a value filter something like:

{
--
-- get only the log entries having a data node in any position (//* syntax)
-------------------------------------------------
SELECT [entry]
       , [entry].value(N'(./*/text())[1]', N'nvarchar(max)')              AS [value]
       , [entry].value(N'(./*/@application)[1]', N'sysname')              AS [application]
       , [entry].value(N'(./*/@timestamp)[1]', N'datetime')               AS [timestamp]
       , [entry].query(N'(./*/data/*)[1]')                                AS [data]
       , [entry].value(N'(./*/special_note/text())[1]', N'nvarchar(max)') AS [special_note]
FROM   @entry AS [entry]
       CROSS APPLY [entry].[nodes]('/*') AS [table] ( [column] )
WHERE  CAST([table].[column].[query]('fn:local-name(.)') AS [SYSNAME]) = N'log'
        AND [entry].value(N'(./*/text())[1]', N'nvarchar(max)')  = 'This is a log item.';
}

That should get you started.

Vipul Dubey · Accepted Answer · 2017-11-20 21:40:53Z

0

SELECT * FROM mytable where mycolumn.value('/TxMsg/VehicleId[1]','nvarchar(max)') LIKE 'QWE123'

I hope Column 'mycolumn' is set as 'xml' data type in table 'mytable'. You need to use the 'value()' function to extract the data out of Xml. Hope this works.

answered Nov 20, 2017 at 21:40

Vipul Dubey

979 bronze badges

Collectives™ on Stack Overflow

Query table using values in XML column

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related