August 12, 2009

SQL SERVER: Check if Node exists in XML or not






Today, I have one requirement to check dynamically if a node exists in my xml or NOT.

I have a stored procedure that receives XML and I need to check if the message information xml contains one Node or NOT. If that node exists then I need to execute that Stored Procedure by different logic and if not it should run with different logic.

I figure it out by using EXISTS.

This is my XML, that I got as parameter.

DECLARE @ExportData  XML
SELECT @ExportData =
'<Data Number="A123">
  <BulkData>
    <EachData Parts="Test1" />
    <EachData Parts="Test2" />
    <EachData Parts="Test3" />
  </BulkData>
</Data>'
Now I need to check if "BulkData" node exists in XML, then I need to write different logic to get the result.
So, I used this

SELECT @ExportData.exist('(//BulkData)')
This will return "1" if node is exists else return "0".

That's it. I can write based on the return result by this statement.

Let me know if it helps you.

Reference : Tejas Shah(http://www.SQLYoga.com)

15 comments:

  1. Good one! Helped me

    ReplyDelete
  2. Thanks as well. Once comment. Routinely I see people using "//" in xpaths. Bear in mind that when this criteria is entered as the only criteria or as the beginning part of the criteria as in this example //BulkData the parser will search the entire document and all node levels for any existance of BulkData. While a small document has likely little impact, larger documents can be very signifcant. Better to explicitly set the path as "/Data/BulkData" which will perform the best.

    ReplyDelete
  3. Very helfpful... thanks for sharing...

    ReplyDelete
  4. It worked!! Thanks a lot

    ReplyDelete
  5. I have a very big xml file,which are coming from SQL table column,the xml files are very big and its saved in xml column in the table.I need to convert all the nodes into column and the values inside nodes as rows.I have done some of the developement where I can see all the Nodes and its values but in the rows.I only need to show all the node values in table column in selec t query

    ReplyDelete
  6. Hi,

    Can you please provide some sample data with expected output? So will try to solve your problem.

    Thanks,
    Tejas
    SQLYoga

    ReplyDelete
  7. Hi,Thanks a lot for Instatnt reply.
    I have a big xml as below:
    -
    -
    2.6

    false

    -
    -
    Personal

    Test DueDateNew
    7
    2012-08-23
    2012-08-24T12:20:58.093




    Unchecked
    -
    -
    Unchecked
    2012-08-23
    Here,I have created one query,whihc is taking data from all Nodes and Values,the query is as below:

    ;WITH XMLNAMESPACES('http://www.Google.co.k/f/1.0' as xsi)


    SELECT
    T.C.value('local-name(.)','nvarchar(max)'),
    T.C.value('(/.)', 'varchar(500)'),
    T.C.query ('declare namespace MI="http://www.Google.co.u/f/1.0";
    ( /*/*)') ,
    T.C.value('(//*[local-name()!=" "])[1]', 'nvarchar(max)')


    FROM [DB].[Table] Cross apply
    xml.nodes('//*') AS T(C);


    But,the nodes name coming in the rows need to come in the columns and the values in rows.
    If also I can create any table,where all the columns could be created as per nodes and then we can insert the values and select.
    Please provide help.
    Regards,
    Nitesh

    ReplyDelete
  8. Sorry,I was not able to send full xml due to size limit.But its really a big xml approx 3600 nodes.

    ReplyDelete
  9. Hi Nitesh,

    Can you have some part of the XML, so can understand the structure of the XML along with expected output?

    Tejas
    SQLYoga

    ReplyDelete


  10. 2.6

    false



    Personal



    2012-06-27
    2012-09-06T13:00:00.000



    B U


    B U
    2012-06-27


    L


    L
    CreateCase
    rner



    Pr

    Point of Sale
    Partial








    Advised






    Client
    Individual
    Prospect


    Prospect
    CreateCase
    A G



    192

    BC

    BC

    YZ
    Male
    10
    Married


    Residential Address
    Current


    1.222


    AB
    CD
    RG
    CN



    123456789










    false


    false



    false
























    false





    Import








    ReplyDelete
  11. Hi Nitesh,

    Can you please send an email with attachment of XML file to tejas[at]sqlyoga.com?

    Thanks,
    Tejas

    ReplyDelete
  12. I have provided full xml to you.Please suggest.

    ReplyDelete
  13. Hi Nitesh,

    Can you please send an email with attachment of XML file to tejas[at]sqlyoga.com?

    Thanks,
    Tejas

    ReplyDelete
  14. Hi,any update on this please?

    ReplyDelete
  15. Hi Nitesh,

    Can you please send an email with attachment of XML file to tejas[at]sqlyoga.com? Once I have XML file, I will try to solve.

    Thanks,
    Tejas
    SQLYoga

    ReplyDelete