Post reply

print XML attribute value

  • January 24, 2017 at 3:29 am

    #334409

    I am trying to get value of an attribute from XML in SQL table
    <?xml version="1.0" encoding="utf-8"?>
    <container>
     <Property Name="paramA" Vocabulary="someVocu">
     <Property Name="paramB" Value="valueA" />
     <Property Name="paramC" Value="valueB" /> </Property>
     <Property Name="paramA" Vocabulary="anotherVocu">
     <Property Name="paramB" Value="valueY" />
     <Property Name="paramC" Value="valueZ" />
     </Property></container>

    select x.XmlCol.value('(Property[@Name="paramB"]/@Value)[1]', 'varchar(50)')  from tempTbl
    CROSS APPLY rawxml.nodes('/container') AS x(XmlCol)

    I am trying to print "valueA" and "valueY" I am getting a NULL.How can I do this?

    Thank you
    Rash

  • January 24, 2017 at 3:56 am

    #1924577

    rash3554 - Tuesday, January 24, 2017 3:29 AM

    I am trying to get value of an attribute from XML in SQL table
    <?xml version="1.0" encoding="utf-8"?>
    <container>
     <Property Name="paramA" Vocabulary="someVocu">
     <Property Name="paramB" Value="valueA" />
     <Property Name="paramC" Value="valueB" /> </Property>
     <Property Name="paramA" Vocabulary="anotherVocu">
     <Property Name="paramB" Value="valueY" />
     <Property Name="paramC" Value="valueZ" />
     </Property></container>

    select x.XmlCol.value('(Property[@Name="paramB"]/@Value)[1]', 'varchar(50)')  from tempTbl
    CROSS APPLY rawxml.nodes('/container') AS x(XmlCol)

    I am trying to print "valueA" and "valueY" I am getting a NULL.How can I do this?

    Thank you
    Rash

    The property element is nested under another property element, adjust the path in the value function.
    😎

    <container>
    <Property Name="paramA" Vocabulary="someVocu">
      <Property Name="paramB" Value="valueA" />
      <Property Name="paramC" Value="valueB" />
    </Property>
    <Property Name="paramA" Vocabulary="anotherVocu">
      <Property Name="paramB" Value="valueY" />
      <Property Name="paramC" Value="valueZ" />
    </Property>
    </container>

    This works:
    select x.XmlCol.value('(Property/Property[@Name="paramB"]/@Value)[1]', 'varchar(50)') from tempTbl 
    CROSS APPLY rawxml.nodes('/container') AS x(XmlCol)

  • January 24, 2017 at 4:11 am

    #1924580

    Thanks.
    But this prints only valueA from <Property Name="paramB" Value="valueA" />

    and not valueY from
    <Property Name="paramB" Value="valueY" />
    How do I iterate through all Property elements?

    Thanks
    Rash

  • January 24, 2017 at 6:26 am

    #1924609

    rash3554 - Tuesday, January 24, 2017 4:11 AM

    Thanks.
    But this prints only valueA from <Property Name="paramB" Value="valueA" />

    and not valueY from
    <Property Name="paramB" Value="valueY" />
    How do I iterate through all Property elements?

    Thanks
    Rash

    Does this help?

    declare @xml xml =
    '<container>
    <Property Name="paramA" Vocabulary="someVocu">
         <Property Name="paramB" Value="valueA" />
         <Property Name="paramC" Value="valueB" />
    </Property>
    <Property Name="paramA" Vocabulary="anotherVocu">
    <Property Name="paramB" Value="valueY" />
    <Property Name="paramC" Value="valueZ" />
    </Property>
    </container>'

    select t.c.value('(@Value)[1]', 'varchar(10)')
    from @xml.nodes('/container/Property/Property') t (c)
    where t.c.value('(@Name)[1]','varchar(10)') = 'paramB'

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • January 24, 2017 at 6:46 am

    #1924613

    rash3554 - Tuesday, January 24, 2017 4:11 AM

    Thanks.
    But this prints only valueA from <Property Name="paramB" Value="valueA" />

    and not valueY from
    <Property Name="paramB" Value="valueY" />
    How do I iterate through all Property elements?

    Thanks
    Rash

    Change the XPath to iterate through the Property nodes
    😎

    DECLARE @TTXML TABLE (TXML XML);
    INSERT INTO @TTXML(TXML) VALUES( '<?xml version="1.0" encoding="utf-8"?><container>
      <Property Name="paramA" Vocabulary="someVocu">
        <Property Name="paramB" Value="valueA" />
        <Property Name="paramC" Value="valueB" />
      </Property>
      <Property Name="paramA" Vocabulary="anotherVocu">
        <Property Name="paramB" Value="valueY" />
        <Property Name="paramC" Value="valueZ" />
      </Property>
    </container>');

    SELECT
        PROP    .DATA.value('@Name' ,'VARCHAR(50)') AS PARAM_NAME
           ,PROP.DATA.value('@Value','VARCHAR(50)') AS PARAM_VALUE
    FROM @TTXML TX
    CROSS APPLY TX.TXML.nodes('/container/Property/Property[@Name="paramB"]') AS PROP(DATA);='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>

  • January 24, 2017 at 6:52 am

    #1924619

    Eirikur Eiriksson - Tuesday, January 24, 2017 6:46 AM

    rash3554 - Tuesday, January 24, 2017 4:11 AM

    Thanks.
    But this prints only valueA from <Property Name="paramB" Value="valueA" />

    and not valueY from
    <Property Name="paramB" Value="valueY" />
    How do I iterate through all Property elements?

    Thanks
    Rash

    Change the XPath to iterate through the Property nodes
    😎

    DECLARE @TTXML TABLE (TXML XML);
    INSERT INTO @TTXML(TXML) VALUES( '<?xml version="1.0" encoding="utf-8"?><container>
      <Property Name="paramA" Vocabulary="someVocu">
        <Property Name="paramB" Value="valueA" />
        <Property Name="paramC" Value="valueB" />
      </Property>
      <Property Name="paramA" Vocabulary="anotherVocu">
        <Property Name="paramB" Value="valueY" />
        <Property Name="paramC" Value="valueZ" />
      </Property>
    </container>');

    SELECT
        PROP    .DATA.value('@Name' ,'VARCHAR(50)') AS PARAM_NAME
           ,PROP.DATA.value('@Value','VARCHAR(50)') AS PARAM_VALUE
    FROM @TTXML TX
    CROSS APPLY TX.TXML.nodes('/container/Property/Property[@Name="paramB"]') AS PROP(DATA);='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>

    Thanks so much. I am not very comfortable with XML parsing in SQL server. Can you suggest any tutorials

  • January 24, 2017 at 7:28 am

    #1924625

    You are very welcome
    😎

    I did post few resources few weeks back on this thread

  • January 24, 2017 at 11:39 am

    #1924698

    Just a quick thought, if you don't know the actual structure of the XML but you do know the name of the node element and a property value of that element then you can use this traversing syntax to find those elements
    😎

    SELECT
      PROP.DATA.value('@Name' ,'VARCHAR(50)') AS PARAM_NAME
     ,PROP.DATA.value('@Value','VARCHAR(50)') AS PARAM_VALUE
    FROM @TTXML TX
    CROSS APPLY TX.TXML.nodes('//Property[@Name="paramB"]') AS PROP(DATA);

Viewing 8 posts - 1 through 7 (of 7 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy