Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2010
    Posts
    1

    Unanswered: XQuery Return All Occurrences of an ellement

    Hi guys,

    Firstly this is my first post so Hi to everyone on here, especially those who are able to help with my issue

    I've had to give myself a crash course on working with xml data in SQL Server and have been successful to a point in achieving what I'm after but I've hit a wall.

    Given the following XML (simplified version of what I'm working with):

    Code:
    
    DECLARE @XML AS XML
    SET @XML = '<Repairs>
    <Repair>
    <RepairNumber>R1</RepairNumber>
    <RepairLines>
    <RepairLine>
    <LineNumber>R1-1</LineNumber>
    <LineDetail>first line for repair 1</LineDetail>
    </RepairLine>
    <RepairLine>
    <LineNumber>R1-2</LineNumber>
    <LineDetail>second line for repair 1</LineDetail>
    </RepairLine>
    <RepairLine>
    <LineNumber>R1-3</LineNumber>
    <LineDetail>third line for repair 1</LineDetail>
    </RepairLine>
    </RepairLines>
    </Repair>
    </Repairs>'
    
    SELECT @XML.value('(/Repairs/Repair/RepairLines/RepairLine/LineNumber)[1]', 'VARCHAR(5)')
    Brilliant if I want to return the first LineNumber of the first Repair. My requirement however is to list all of the line numbers (a row returned for each).

    Please god can someone help me because I want to go to sleep!!!

    Many many thanks

    A bonus question (not a requirement but I'd like to know how) ... how would i return (if the xml data had it) the 3rd LineNumber of the 2nd Repair?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Code:
    select
       c.value('(LineNumber)[1]','VARCHAR(5)')
      ,c.value('(LineDetail)[1]','VARCHAR(25)')
       from  @xml.nodes('/Repairs/Repair/RepairLines/RepairLine') t(c)
    
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •