CODEWITHSUNDEEP
sqlsql-serverxmlxpathsql-server-2008-r2
nakakapagpabagabag
nakakapagpabagabag

Reputation: 489

Query table using values in XML column

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'

Upvotes: 1

Views: 84

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

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;

Upvotes: 1

Vipul Dubey
Vipul Dubey

Reputation: 97

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.

Upvotes: 0

Katherine Elizabeth Lightsey
Katherine Elizabeth Lightsey

Reputation: 853

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.

Upvotes: 0

Related Questions