Post reply

extract a value from xml

  • August 19, 2010 at 9:35 am

    #98890

    how would i extract the code "Alpa125" from the below xml string ?

    Declare @XmlStr as Xml

    set @XmlStr = '<XObjects><Objects Type="Alp" code="Alpa125" pChild="0" grp="0" /></XObjects>'

    thanks

    ron

  • August 19, 2010 at 1:57 pm

    #1209351

    One way to do this would be:

    DECLARE @XmlStr XML;

    SET @XmlStr = '<XObjects><Objects Type="Alp" code="Alpa125" pChild="0" grp="0" /></XObjects>' ;

    SELECT Nodes.value(N'@code', N'nvarchar(10)') AS CodeValue

    FROM @XmlStr.nodes(N'/XObjects/Objects') XObjects(Nodes);

    Note that the value of the node is cast to navarchar(10) above - cast it to an appropriate length to avoid truncation (depending on the data in the XML)

  • August 19, 2010 at 2:07 pm

    #1209357

    If you have only one root node in the XML then the following gives better performance:

    SELECT Nodes.value(N'@code', N'nvarchar(10)') AS CodeValue

    FROM @XmlStr.nodes(N'/XObjects[1]/Objects') XObjects(Nodes);

    The change here is in the " FROM @XmlStr.nodes(N'/XObjects[1]/Objects') " -

  • August 20, 2010 at 9:08 am

    #1209729

    Perfect.thanks, winash

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

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