12-15-10, 14:33 #1Registered User
- Join Date
- Dec 2010
Unanswered: XQuery Return All Occurrences of an ellement
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):
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)', 'VARCHAR(5)')
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?
12-15-10, 19:07 #2Registered User
- Join Date
- Jul 2003
- San Antonio, TX
select c.value('(LineNumber)','VARCHAR(5)') ,c.value('(LineDetail)','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."