locked
XPATH query for date range RRS feed

  • Question

  • Hi,

    I am pulling the data into a column as XML data type and I want to query within the below XML that is returned.

    I want to construct XPATH that allows me to search for a date range where Field Name = Review Date. I want records to be returned whenever the Review Date falls within a date range that I am passing from the UI

    <tr2 fieldcode="23" fieldvalue="Periodic Review">

      <LF FieldName="Request Type" />

    </tr2>

    <tr2 fieldcode="26" fieldvalue="05/04/2010">

      <LF FieldName="Review Date" />

    </tr2>

    <tr2 fieldcode="27">

      <LF FieldName="Total Number of Accounts" />

    </tr2>

    <tr2 fieldcode="28">

      <LF FieldName="Total Number of Roles" />

    </tr2>

    <tr2 fieldcode="27" fieldvalue="28">

      <LF FieldName="Total Number of Accounts" />

    </tr2>

    <tr2 fieldcode="28" fieldvalue="252">

      <LF FieldName="Total Number of Roles" />

    </tr2>

    Friday, October 22, 2010 3:49 PM

Answers

  • Something like this:

    DECLARE @t TABLE ( Id INT IDENTITY, yourXML XML )
    
    INSERT INTO @t
    SELECT '<tr2 fieldcode="23" fieldvalue="Periodic Review">
     <LF FieldName="Request Type" />
    </tr2>
    <tr2 fieldcode="26" fieldvalue="05/04/2010">
     <LF FieldName="Review Date" />
    </tr2>
    <tr2 fieldcode="27">
     <LF FieldName="Total Number of Accounts" />
    </tr2>
    <tr2 fieldcode="28">
     <LF FieldName="Total Number of Roles" />
    </tr2>
    <tr2 fieldcode="27" fieldvalue="28">
     <LF FieldName="Total Number of Accounts" />
    </tr2>
    <tr2 fieldcode="28" fieldvalue="252">
     <LF FieldName="Total Number of Roles" />
    </tr2>'
    
    -- SELECT * FROM @t
    
    DECLARE @startDate CHAR(10)
    DECLARE @endDate CHAR(10)
    SELECT @startDate = '05/04/2010', @endDate = '05/04/2010'
    
    SELECT 
    	t.*,
    	x.y.query('.') AS result,	
    	x.y.value('@fieldcode', 'int') AS fieldcode
    FROM @t t
    	CROSS APPLY t.yourXML.nodes('tr2[@fieldvalue >= sql:variable("@startDate") and @fieldvalue <= sql:variable("@startDate")][LF/@FieldName = "Review Date"]') x(y)
    
    
    • Proposed as answer by HunchbackMVP Tuesday, October 26, 2010 12:04 AM
    • Marked as answer by Ai-hua Qiu Monday, November 1, 2010 9:12 AM
    Friday, October 22, 2010 10:58 PM
    Answerer