SQL Server 2000 sp_xml_preparedocument - To get innerxml/innertext of a node
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="shipS开发者_开发百科ourceType">INTERNAL</Info>
<Data>
<Item>
<ItemID>
<PartID>1234</PartID>
<PartAuxiliaryID>
<Info name="quoteNumber">962445</Info>
<Info name="shipSourceType">INTERNAL</Info>
</PartAuxiliaryID>
</ItemID>
</Item></Data>
I found the answer
Option 1
DECLARE @xmlData varchar(3000)
SET @xmlData = '<Data>
<Item>
<ItemID>
<PartID>1234</PartID>
<PartAuxiliaryID>
<Info name="quoteNumber">962445</Info>
<Info name="shipSourceType">INTERNAL</Info>
</PartAuxiliaryID>
</ItemID>
</Item></Data>'
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 = '<Data>
<Item>
<ItemID>
<PartID>1234</PartID>
<PartAuxiliaryID>
<Info name="quoteNumber">962445</Info>
<Info name="shipSourceType">INTERNAL</Info>
</PartAuxiliaryID>
</ItemID>
</Item></Data>'
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>\
精彩评论