SQL Server 2000 sp_xml_preparedocument - To get innerxml/innertext of a node

Question

I have some data like at the bottom. I use SQL Server 2000 stored proc to process this data using sp_xml_preparedocument .

I would like to get the data within the node PartAuxiliaryID as it is below(the exact xml as it is, not the values or attribute) to a variable. What will be the best way to do that in stored proc using sp_xml_preparedocument

     <Info name="quoteNumber">962445</Info> 
     <Info name="shipSourceType">INTERNAL</Info> 

---

  <Data> 
  <Item>
   <ItemID>
       <PartID>1234</PartID>
        <PartAuxiliaryID>
          <Info name="quoteNumber">962445</Info> 
           <Info name="shipSourceType">INTERNAL</Info> 
        </PartAuxiliaryID>
   </ItemID>
  </Item></Data>

Answer 1

I found the answer

Option 1

DECLARE @xmlData varchar(3000) SET @xmlData = ' 1234 962445 INTERNAL '

DECLARE @idoc int, @intRet int DECLARE @URL VARCHAR(1000) EXEC @intRet= sp_xml_preparedocument @idoc OUTPUT, @xmlData

SELECT * FROM OPENXML (@idoc, '/Data/Item/ItemID/PartAuxiliaryID') WITH (PartAuxiliaryID varchar(2000) '@mp:xmltext')

EXEC sp_xml_removedocument @idoc

--will get the following

<PartAuxiliaryID><Info name="quoteNumber">962445</Info><Info name="shipSourceType">INTERNAL</Info></PartAuxiliaryID>

Option 2

DECLARE @xmlData varchar(3000) SET @xmlData = ' 1234 962445 INTERNAL '

DECLARE @idoc int, @intRet int DECLARE @URL VARCHAR(1000) EXEC @intRet= sp_xml_preparedocument @idoc OUTPUT, @xmlData

SELECT * FROM OPENXML (@idoc, '/Data/Item/ItemID/PartAuxiliaryID/Info') WITH (PartAuxiliaryID varchar(2000) '@mp:xmltext')

EXEC sp_xml_removedocument @idoc

will get the following as two rows

   <Info name="quoteNumber">962445</Info>
   <Info name="shipSourceType">INTERNAL</Info>\