SQL: How do I select an XML element from within a larger variable of type XML?

Question

I am sending multiple different data items to a stored procedure in an XML variable. The signature of the function goes something like this:

CREATE PROCEDURE MyProc
   @id INT,
   @xml xml
AS
BEGIN
....
END

and the XML looks somewhat like this:

<Root>
   <SampleTime>2009-02-05 13:25:43</SampleTime>
   <Gizmo1>
     <Voltage>34.1</Voltage>
     <Temperature>78.3</Temperature>
   </Gizmo1>
   <Gizmo2>
      <Weight>235</Weight>
      <Exposure>North</Exposure>
   </Gizmo2>
</Root>

where the different GizmoX contain unrelated information. When I want to extract a single field from the XML, I've had pretty good luck with SELECT FROM xml.Nodes(), as in

SELECT T.item.value('Voltage[1]', 'float') as Voltage
FROM @xml.nodes('//Root/Gizmo1') T(item)

Now the problem I have is that I want to extract the whole Gizmo element in another xml variable. the values() function does not accept data type 'xml', and I don't know what to select anyway.

Does anybody have a solution for this?

Answer 1

Use XQuery:

select @xml.query('//Root/Gizmo1')