Hi,

Can this help:

http://www.mssqltips.com/tip.asp?tip=1609
Permalink | Report

Hi,

This T-SQL will do what you asked for, but it would need to be modified if you have more than one element to work with. I couldn't tell from your example exactly what the schema for the incoming XML would be like.

DECLARE @xml XML
DECLARE @xml_new XML
DECLARE @idoc INT

-- your sample xml
SET @xml = ''

--create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml

-- USE OPENXML to SELECT attributes out of @xml, then use "FOR XML" to create new xml
SET @xml_new = (
SELECT Type, Term, DI, PO
FROM OPENXML (@idoc, '/RB/RI',1)
WITH (
Type VARCHAR(16)
,Term INT
,DI INT
,PO INT
)
FOR XML PATH('RI')
)

-- done with XML document
EXEC sp_xml_removedocument @idoc
Permalink | Report