locked
How to read XML colum from sql table which has all XML row has root node different RRS feed

  • Question

  • hi

    i have XML column in sql as below table  temp

    id XMLCOL

    1    <H2><permit>< code xlmns =namespace>N</code>other tage....<code xlmns

    =namespace>V</code></permit></h2>

    2    <T2><permit><code xmlns=namespace>B</code></permit></T2>

    3    <T3><permit><code>TT</code></permit></T3>

    here my root node are different.

    i fire this query

     select
    N.C.value('code[1]', 'varchar(50)')  as DDLPC2
     from #temp t cross apply t.rawmessage.nodes('//permit') N(C)

    but it didnt work. i need code value. suppose 1 column hase 2 code value then it display me N,V

    it has differnet name sapce
    • Edited by Meerack Wednesday, March 8, 2017 5:07 PM
    Wednesday, March 8, 2017 4:05 PM

Answers

  •  

    solve it

    ;with XMLNAMESPACES(
                        'schemaname' AS fb)
    SELECT 
         rawmessageid,FkMessageType as MessageType,PartitionID,MessageOrigin,
          rawmessage,
          rawmessage.value('(//fb:node)[1]', 'varchar(max)') as DDLPC2_1

    FROM  table

     
    • Marked as answer by Meerack Wednesday, March 8, 2017 5:44 PM
    Wednesday, March 8, 2017 5:44 PM