none
Using XQuery and Exists to check for attribute RRS feed

  • Question

  • Hi I am trying to query some XML to determine if an attribute exists within the element. I must be missing something as nothing I try works.

    My Query currently is 

        SELECT name, 

       username, 
       CAST(processxml as xml).value('count(//stage[@type="Exception"]/exception[@type=""][@detail=""])', 'int') as Result
        FROM bpaprocess
        JOIN bpauser 
          ON userid = lastmodifiedby  
    where CAST(processxml as xml).exist('/stage/exception[@usecurrent]') ????
      and name = 'Lab 10 Process End - Bing'

    I want to know if the "usecurrent" attribute does not exist within the /stage/exception element. Two XML examples are below:  

    <stage name="System Error" type="Exception" stageid="401474d0-d940-45bd-bbb7-944e4939ae3c">
    <subsheetid>a84a2231-cf01-4025-8eb5-0842aa4f4c12</subsheetid>
    <display y="180" x="45"/>
    <exception type="" detail="" localized="yes"/>

    </stage>

    <stage name="System Exception" type="Exception" stageid="001d3e3a-3594-4624-a2ca-59b4d1e46646">
    <subsheetid>8fd2bd86-10e1-4a35-8041-7659e7be3612</subsheetid>
    <display y="240" x="-15"/>
    <exception type="" detail="" usecurrent="yes" localized="yes"/>
    </stage>

    Appreciate your help!

    Wednesday, November 6, 2019 5:26 AM

Answers

  • Check this:

    . . .
    where
    CAST(processxml as xml).exist('//stage/exception/@usecurrent') = 0

    • Edited by Viorel_MVP Wednesday, November 6, 2019 6:10 AM
    • Marked as answer by fiddlerontheroof Thursday, November 7, 2019 12:09 AM
    Wednesday, November 6, 2019 6:10 AM
  • Hi fiddlerontheroof,

    Here is SQL to accommodate a second condition: attribute @type exists and its value is ""

    -- DDL and sample data population, start
    DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, processxml NVARCHAR(MAX));
    INSERT INTO @tbl (processxml)
    VALUES
    (N'<stage name="System Error" type="Exception" stageid="401474d0-d940-45bd-bbb7-944e4939ae3c">
    	<subsheetid>a84a2231-cf01-4025-8eb5-0842aa4f4c12</subsheetid>
    	<display y="180" x="45"/>
    	<exception type="" detail="" localized="yes"/> 
    </stage>')
    , ('<stage name="System Exception" type="Exception" stageid="001d3e3a-3594-4624-a2ca-59b4d1e46646">
    	<subsheetid>8fd2bd86-10e1-4a35-8041-7659e7be3612</subsheetid>
    	<display y="240" x="-15"/>
    	<exception type="" detail="" usecurrent="yes" localized="yes"/>
    </stage>');
    -- DDL and sample data population, end
    
    SELECT *
    	, CAST(processxml AS XML) AS xml_data
    FROM @tbl
    WHERE CAST(processxml AS XML).exist('/stage/exception/@usecurrent') = 0
    	AND CAST(processxml AS XML).value('(/stage/exception/@type)[1]','VARCHAR(10)') = '';



    Thursday, November 7, 2019 12:17 AM
  • Try this single condition too:

    WHERE CAST(processxml as xml).exist('//stage/exception[@type="" and not(@usecurrent)]') = 1

     

    Thursday, November 7, 2019 5:37 AM

All replies

  • Check this:

    . . .
    where
    CAST(processxml as xml).exist('//stage/exception/@usecurrent') = 0

    • Edited by Viorel_MVP Wednesday, November 6, 2019 6:10 AM
    • Marked as answer by fiddlerontheroof Thursday, November 7, 2019 12:09 AM
    Wednesday, November 6, 2019 6:10 AM
  • Hi fiddlerontheroof,

    Just as Viorel pointed out, it is a simple WHERE clause. All credit goes to Viorel.

    Here is a full example to run, test, and feel it in SSMS:

    -- DDL and sample data population, start
    DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, processxml NVARCHAR(MAX));
    INSERT INTO @tbl (processxml)
    VALUES
    (N'<stage name="System Error" type="Exception" stageid="401474d0-d940-45bd-bbb7-944e4939ae3c">
    	<subsheetid>a84a2231-cf01-4025-8eb5-0842aa4f4c12</subsheetid>
    	<display y="180" x="45"/>
    	<exception type="" detail="" localized="yes"/> 
    </stage>')
    , ('<stage name="System Exception" type="Exception" stageid="001d3e3a-3594-4624-a2ca-59b4d1e46646">
    	<subsheetid>8fd2bd86-10e1-4a35-8041-7659e7be3612</subsheetid>
    	<display y="240" x="-15"/>
    	<exception type="" detail="" usecurrent="yes" localized="yes"/>
    </stage>');
    -- DDL and sample data population, end
    
    SELECT *
    	, CAST(processxml AS XML) AS xml_data
    FROM @tbl
    WHERE CAST(processxml AS XML).exist('/stage/exception/@usecurrent') = 0;
    

    Viorel,

    What is your e-mail? I would like to connect with you. Please drop me a line ykhabins at bellsouth dot net

    Wednesday, November 6, 2019 3:38 PM
  • Thank you Viorel and Yitzhak. Wondering if your could help with one tiny change?

    Your answer does indeed work, however I would like to tweak the query so that it reports the missing "usecurrent" attribute only if the "type" attribute = "". 

    DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, processxml NVARCHAR(MAX));
    INSERT INTO @tbl (processxml)
    VALUES
    (N'<stage name="System Error" type="Exception" stageid="401474d0-d940-45bd-bbb7-944e4939ae3c">
    <subsheetid>a84a2231-cf01-4025-8eb5-0842aa4f4c12</subsheetid>
    <display y="180" x="45"/>
    <exception type="" detail="" localized="yes"/> 
    </stage>')
    , ('<stage name="System Exception" type="Exception" stageid="001d3e3a-3594-4624-a2ca-59b4d1e46646">
    <subsheetid>8fd2bd86-10e1-4a35-8041-7659e7be3612</subsheetid>
    <display y="240" x="-15"/>
    <exception type="" detail="" usecurrent="yes" localized="yes"/>
    </stage>')
    ,('<stage stageid="e9fb902e-100a-4278-a916-0b81b25903ba" name="Salami - Program ID" type="Storage">
    <narrative></narrative>
    <displayx>-240</displayx>
    <displayy>15</displayy>
    <displaywidth>90</displaywidth>
    <displayheight>30</displayheight>
    <font family="Segoe UI" size="10" style="Regular" color="000000" /><datatype>text</datatype>
    <initialvalue xml:space="preserve">04ce00be-84cf-4890-871c-33c348e2fdb7</initialvalue>
    <exposure>Always</exposure>
    <alwaysinit />
    </stage>');
    -- DDL and sample data population, end

    SELECT *
    , CAST(processxml AS XML) AS xml_data
    FROM @tbl
    WHERE CAST(processxml as xml).exist('//stage/exception/@type') = 1
      and CAST(processxml AS XML).exist('//stage/exception/@usecurrent') = 0;

    Appreciate your help.

    Thursday, November 7, 2019 12:09 AM
  • Hi fiddlerontheroof,

    Here is SQL to accommodate a second condition: attribute @type exists and its value is ""

    -- DDL and sample data population, start
    DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, processxml NVARCHAR(MAX));
    INSERT INTO @tbl (processxml)
    VALUES
    (N'<stage name="System Error" type="Exception" stageid="401474d0-d940-45bd-bbb7-944e4939ae3c">
    	<subsheetid>a84a2231-cf01-4025-8eb5-0842aa4f4c12</subsheetid>
    	<display y="180" x="45"/>
    	<exception type="" detail="" localized="yes"/> 
    </stage>')
    , ('<stage name="System Exception" type="Exception" stageid="001d3e3a-3594-4624-a2ca-59b4d1e46646">
    	<subsheetid>8fd2bd86-10e1-4a35-8041-7659e7be3612</subsheetid>
    	<display y="240" x="-15"/>
    	<exception type="" detail="" usecurrent="yes" localized="yes"/>
    </stage>');
    -- DDL and sample data population, end
    
    SELECT *
    	, CAST(processxml AS XML) AS xml_data
    FROM @tbl
    WHERE CAST(processxml AS XML).exist('/stage/exception/@usecurrent') = 0
    	AND CAST(processxml AS XML).value('(/stage/exception/@type)[1]','VARCHAR(10)') = '';



    Thursday, November 7, 2019 12:17 AM
  • Try this single condition too:

    WHERE CAST(processxml as xml).exist('//stage/exception[@type="" and not(@usecurrent)]') = 1

     

    Thursday, November 7, 2019 5:37 AM
  • Hi fiddlerontheroof,

    Viorel's solution is better. It is doing one single CAST() operation.

    Viorel Bejan,

    What is your e-mail? I would like to connect with you. Please drop me a line ykhabins at bellsouth dot net


    Thursday, November 7, 2019 5:59 AM
  • Thank you so much Viorel and Yitzhak, I finally get it!
    Thursday, November 7, 2019 9:13 PM
  • Sorry to hassle, but I have one remaining problem.

    I am attempting to display the "name" attribute of all the elements my query has identified. The where clause is returning what I expect, but I cannot for the life of me access the "name" attribute. My query returns the name attribute of a different element. I am assuming it is something to do with the [1] but am unsure.

    Query

       

    SELECT name, 
       username,
       CAST(processxml as xml).value('/process[1]/stage[@name][1]','varchar(256)') as Stage 
        FROM bpaprocess
        JOIN bpauser 
          ON userid = lastmodifiedby  
    where CAST(processxml as xml).exist('//stage[@type = "SubSheetInfo" and not(@narrative)]') = 1
       and name = 'blah'

    Stage returns 1aa1a327-7c0a-4bb3-b311-ab225ea85d1c which is the "stageid" attributute of a different element.

    XML

    <?xml version="1.0"?>
    -<process preferredid="2a1374a5-55b5-4542-8318-f08b9fa7f31e" runmode="Exclusive" type="object" byrefcollection="true" narrative="" bpversion="6.6.0.15260" version="1.0" name="blah">
    +<appdef>
    +<view>
    <preconditions/>
    <endpoint narrative=""/>
    +<subsheet type="CleanUp" published="True" subsheetid="835e0b43-bc7a-47d9-b29e-465c78881be3">
    +<subsheet type="Normal" published="False" subsheetid="84142bc7-030d-43b2-b870-f67905f03f9b">
    -<stage type="Start" name="Start" stageid="b4418bc4-bf0e-4c1a-873d-c168497a6069">
    <loginhibit/>
    <display y="-105" x="15"/>
    <onsuccess>1aa1a327-7c0a-4bb3-b311-ab225ea85d1c</onsuccess>
    </stage>

    -<stage type="End" name="End" stageid="1aa1a327-7c0a-4bb3-b311-ab225ea85d1c">
    <loginhibit/>
    <display y="90" x="15"/>
    </stage> <-- my query is retrieving the stageid of this element (I am so confused...)

    -<stage type="ProcessInfo" name="Stage1" stageid="e258e9f9-2800-4d39-aced-cf52e1c8189d">
    <display y="-105" x="-195" h="90" w="150"/>
    +<references>
    -<imports>
    <import>System</import>
    <import>System.Drawing</import>
    <import>System.Data</import>
    </imports>
    <language>visualbasic</language>
    -<globalcode>
    +<![CDATA[]]>
    </globalcode>
    -<code>
    +<![CDATA[]]>
    </code>
    </stage>
    -<stage type="SubSheetInfo" name="Clean Up" stageid="183f70a0-2a8d-4445-be3c-f9f86e93635f">
    <subsheetid>835e0b43-bc7a-47d9-b29e-465c78881be3</subsheetid>
    <display y="-105" x="-195" h="90" w="150"/>
    </stage>
    -<stage type="Start" name="Start" stageid="c8ddeea9-585a-45a1-85bb-1a21a3c99b60">
    <subsheetid>835e0b43-bc7a-47d9-b29e-465c78881be3</subsheetid>
    <loginhibit/>
    <display y="-105" x="15"/>
    <onsuccess>53c7676c-4145-4425-98a3-2278be5b6106</onsuccess>
    </stage>
    -<stage type="End" name="End" stageid="53c7676c-4145-4425-98a3-2278be5b6106">
    <subsheetid>835e0b43-bc7a-47d9-b29e-465c78881be3</subsheetid>
    <loginhibit/>
    <display y="90" x="15"/>
    </stage>

    -<stage type="SubSheetInfo" name="Action 1" stageid="f7421f67-7e1e-4a60-91af-93cad9f84190">
    <subsheetid>84142bc7-030d-43b2-b870-f67905f03f9b</subsheetid>
    <narrative>blah</narrative>
    <display y="-105" x="-195" h="90" w="150"/>
    </stage> <--- trying to retrieve the name attribute from this element


    +<stage type="Start" name="Start" stageid="7ec4cc72-a2f5-4c6f-844f-50c9f0ea0ea0">
    +<stage type="End" name="End" stageid="f2e12146-651d-474a-ae92-6bd47445f8b9">
    +<stage type="Note" name="Note1" stageid="a63773a6-a878-4f63-a3b7-b9899848e5fd">
    +<stage type="Note" name="Note2" stageid="19afbd0a-16de-4b7c-a741-7d6d578c08aa">
    +<stage type="WaitEnd" name="Time Out1" stageid="cca737bc-c4d7-4a7b-96a8-cf57887c41af">
    +<stage type="WaitStart" name="Wait1" stageid="f3f8a63e-b66b-4ba5-9876-7bebcfaf1bf4">
    </process>

    As always, I appreciate your help.

    Friday, November 8, 2019 11:22 PM
  • Hi fiddlerontheroof,

    I used CTE to make data type conversion into XML just once and modified SQL/XQuery to return name attribute.

    SQL:

    -- DDL and sample data population, start
    DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, processxml NVARCHAR(MAX));
    INSERT INTO @tbl (processxml)
    VALUES
    (N'<stage name="System Error" type="Exception" stageid="401474d0-d940-45bd-bbb7-944e4939ae3c">
    	<subsheetid>a84a2231-cf01-4025-8eb5-0842aa4f4c12</subsheetid>
    	<display y="180" x="45"/>
    	<exception type="" detail="" localized="yes"/> 
    </stage>')
    , ('<stage name="System Exception" type="Exception" stageid="001d3e3a-3594-4624-a2ca-59b4d1e46646">
    	<subsheetid>8fd2bd86-10e1-4a35-8041-7659e7be3612</subsheetid>
    	<display y="240" x="-15"/>
    	<exception type="" detail="" usecurrent="yes" localized="yes"/>
    </stage>');
    -- DDL and sample data population, end
    
    ;WITH rs AS
    (
    	SELECT *
    		, CAST(processxml AS XML) AS xml_data
    	FROM @tbl
    )
    SELECT ID
    	, xml_data.value('(/stage/@name)[1]','VARCHAR(30)') AS [name]
    	, xml_data
    FROM rs
    WHERE xml_data.exist('/stage/exception[@type="" and not(@usecurrent)]') = 1;



    Sunday, November 10, 2019 12:27 AM