none
Query From XML RRS feed

  • Question

  • Hello,

       I have write the following syntax :

    declare @XmlData xml = '<employees xmlns="http://www.Test.mov.it.sa">
                                             <employee>
    <NIN>1234567891</NIN>
    <DOB>14381201</DOB>
    <Nationality>EGY</Nationality>
    <Enforce>1</Enforce>
       </employee>
    </employees>'
    SELECT 10, T.Item.value('(DOB)[1]', 'nvarchar(50)') , T.Item.value('(Nationality)[1]', 'nvarchar(50)') , T.Item.value('(NIN)[1]', 'nvarchar(50)'), T.Item.value('(Enforce)[1]', 'bit')
     FROM @XmlData.nodes('employees/employee') AS T(Item)

    • When I run the query there is no data returned .
    • when I remove this line "xmlns="http://www.Test.mov.it.sa" , the data returned

      So How Can I read from xml that has 'xmlns' ?

     Thanks,


    ASk

    Tuesday, April 3, 2018 5:18 AM

Answers

  • Nope you need to just declare the XML namespace and it will work

    see illustration below

    declare @XmlData xml = '<employees xmlns="http://www.Test.mov.it.sa">
                                             <employee>
    <NIN>1234567891</NIN>
    <DOB>14381201</DOB>
    <Nationality>EGY</Nationality>
    <Enforce>1</Enforce>
       </employee>
    </employees>';
    WITH XMLNAMESPACES (DEFAULT 'http://www.Test.mov.it.sa')
    SELECT 10, T.Item.value('(DOB)[1]', 'nvarchar(50)') , T.Item.value('(Nationality)[1]', 'nvarchar(50)') , T.Item.value('(NIN)[1]', 'nvarchar(50)'), T.Item.value('(Enforce)[1]', 'bit')
     FROM @XmlData.nodes('employees/employee') AS T(Item)
    
    
    /*
    Output
    --------------------------------------------
    10	14381201	EGY	1234567891	1
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by himo Tuesday, April 3, 2018 5:58 AM
    Tuesday, April 3, 2018 5:27 AM