Post reply

get data from XML

  • April 4, 2017 at 7:35 am

    #322748

    Hello,
    How can  bypass a block from xml? On the net, all the tutorials looks like this one:
    <?xml version="1.0" encoding="UTF-8"?>
    <note>
      <to>Tove</to>
      <from>Jani</from>
      <heading>Reminder</heading>
      <body>Don't forget me this weekend!</body>
    </note>
    ( from https://www.w3schools.com/xml/)

    But,  what can I do in the following situation?

    USE ClientDB;
    GO
    CREATE TABLE DES_Nomenclator
    (
    ClientID INT PRIMARY KEY IDENTITY,
    Info_untyped XML
    );

    INSERT INTO DES_Nomenclator (Info_untyped)
    VALUES
    (
    '<?xml version="1.0" encoding="UTF-8"?>
    <codeSystem>
         <codeSystemMetadata>
             <codeSystem>2.16.840.1.113883.3.3368.6.15</codeSystem>
             <codeSystemName>ProsthesisTypes</codeSystemName>
             <description>ProsthesisTypes</description>
             <exportDate>2016-11-01 11:25:51</exportDate>
             <lastModifyDate>2014-02-27 00:00:00</lastModifyDate>
         </codeSystemMetadata>
      <values>
            <value code="D" description="Definitiva" validFrom="2000-01-01 00:00:00"></value>
            <value code="T" description="Temporara" validFrom="2000-01-01 00:00:00"></value>
            <value code="e" description="e" validFrom="2014-02-27 00:00:00"></value>
            <value code="e" description="e" validFrom="2014-02-26 00:00:00"></value>
      </values>
    </codeSystem>'
    );

    How can I get:
    Code        Description       validFrom
     D             Definitiva          2001-01-01
     T             Temporara            2000-01-01
     e             e                    2014-02-27
     e             e                    2014-02-26

    I dont know  how to bypass <codeSystemMetadata> and go to values

    I try something  like:
    SELECT Info_untyped.value('(/codeSystem/value/node()) [1]', 'varchar(20)') as Nume
    FROM DES_Nomenclator;

    Thank you in advance,
    Florin

  • April 4, 2017 at 7:54 am

    #1936537

    I think your full path should include "Values".
    /codeSystem/Values/value

  • April 4, 2017 at 7:59 am

    #1936540

    SELECT x.r.value('@code[1]','CHAR(1)') AS Code,
       x.r.value('@description[1]','VARCHAR(20)') AS Description,
       x.r.value('@validFrom[1]','DATE') AS validFrom
    FROM DES_Nomenclator
    CROSS APPLY Info_untyped.nodes('/codeSystem/values/value') AS x(r);

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • April 4, 2017 at 9:21 am

    #1936570

    Try this:

    CREATE TABLE dbo.DES_Nomenclator (
        ClientID int PRIMARY KEY IDENTITY,
        Info_untyped xml
    );

    INSERT INTO DES_Nomenclator (Info_untyped)
        VALUES    ('<?xml version="1.0" encoding="UTF-8"?>
    <codeSystem>
      <codeSystemMetadata>
        <codeSystem>2.16.840.1.113883.3.3368.6.15</codeSystem>
        <codeSystemName>ProsthesisTypes</codeSystemName>
        <description>ProsthesisTypes</description>
        <exportDate>2016-11-01 11:25:51</exportDate>
        <lastModifyDate>2014-02-27 00:00:00</lastModifyDate>
      </codeSystemMetadata>
    <values>
       <value code="D" description="Definitiva" validFrom="2000-01-01 00:00:00"></value>
       <value code="T" description="Temporara" validFrom="2000-01-01 00:00:00"></value>
       <value code="e" description="e" validFrom="2014-02-27 00:00:00"></value>
       <value code="e" description="e" validFrom="2014-02-26 00:00:00"></value>
    </values>
    </codeSystem>');

    SELECT ClientID,
        code = N.node.value('@code', 'char(1)'),
        [description] = N.node.value('@description', 'varchar(10)'),
        validFrom = N.node.value('@validFrom', 'datetime')
    FROM dbo.DES_Nomenclator AS DN
        CROSS APPLY DN.Info_untyped.nodes('/codeSystem/values/value') AS N(node)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • April 5, 2017 at 3:08 am

    #1936731

    Thank you Mark and sgmunson!
    It is working!

Viewing 5 posts - 1 through 4 (of 4 total)

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