Post reply

Reading XML Field from a Table

  • June 5, 2020 at 4:48 pm

    Go to Answer

    #3760907

    I have an xml field  [Risultato] in a Table , the first record is like this:

     <RisultatiCalcolo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <RisultatoA xmlns="http://tempuri.org/">10</RisultatoA>
    <RisultatoB xmlns="http://tempuri.org/">13</RisultatoB>
    <RisultatoC xmlns="http://tempuri.org/">0</RisultatoC>
     </RisultatiCalcolo>

    I need to write a query and add other 3 fields : RisultatoA, RisultatoB and RisultatoC  with the values 10, 13,0 for the first record and so on....

    I don't know how to write it.

    I tried this but doesn't work,return NULL for every record:

    select X.N.value('RisultatoA[1]', 'int') as RisultatoA,
    X.N.value('RisultatoB[1]', 'int') as RisultatoB,
    X.N.value('RisultatoC[1]', 'int') as RisultatoC 
    from [dbo].[Table1] as T cross apply T.Risultato.nodes('/RisultatiCalcolo') as X(N)

    Thank you

     

    • This topic was modified 3 years, 10 months ago by  AntonyO.

  • June 5, 2020 at 7:17 pm

    Answer

    #3760952

    Pretty sure the problem there is with the XML NameSpace (xmlns).  You are defining a namespace in the XML, but then not specifying the namespace in the SQL.  You have 2 options -specify a namespace OR ignore the namespace.

    To specify one, change your SELECT to:

    WITH XMLNAMESPACES ( DEFAULT N'http://tempuri.org/' )
    select X.N.value('RisultatoA[1]', 'int') as RisultatoA,
    X.N.value('RisultatoB[1]', 'int') as RisultatoB,
    X.N.value('RisultatoC[1]', 'int') as RisultatoC 
    from [dbo].[Table1] as T cross apply T.Risultato.nodes('/RisultatiCalcolo') as X(N);

    to ignore the namespace, change your SELECT to:

    select X.N.value('*:RisultatoA[1]', 'int') as RisultatoA,
    X.N.value('*:RisultatoB[1]', 'int') as RisultatoB,
    X.N.value('*:RisultatoC[1]', 'int') as RisultatoC 
    from [dbo].[Table1] as T cross apply T.Risultato.nodes('/RisultatiCalcolo') as X(N)

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • June 8, 2020 at 8:40 am

    #3761246

    The first one doesn't return anything, but the last one (ignoring the name space) it's OK. I'll use that.

    Thank you very much

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply