T-SQL query of an XML column with XPath is very slow - how to improve or alternatives?

Question

I have a table containing an XML type column. The current approach is to use XPath to query for values ​​that are in XML. Unfortunately, this method is extremely slow.

The table contains about 500,000 rows. In fact, this is an intermediate table that receives new data every day, so using XML indexing in a column is not practical - the daily INSERT operation takes longer. Without indexing, it ends in about a minute.

Are there any alternatives for querying XML data that will be much faster?

Answer 1

XML ? ?

, , :

• , XML
• XML XQuery/XPath
• ,

, , ( INT, ) . , - XML; , , , .

:

, BIT XML, , VPN- :

CREATE FUNCTION dbo.GetVPNFlag(@Data XML)
RETURNS BIT
WITH SCHEMABINDING
AS BEGIN
  DECLARE @VPNFlag BIT

  SELECT  
    @VPNFlag = ISNULL(@Data.value('(EntryIP/VPNOption)[1]', 'bit'), 0)

  RETURN @VPNFlag
END

XML, VPN . :

ALTER TABLE dbo.ContractData
  ADD IsVPN AS dbo.GetVPNFlag(XmlData) PERSISTED

XmlData ContractData . BIT, IsVPN ContractData.

VPN :

SELECT (list of fields) 
FROM dbo.ContractData
WHERE IsVPN = 1

Answer 2

, " ". , .