3 years ago
XML transformation using OPENXML in SQL 2005?
I have a SQL 2005 xml variable, @xml, which I need to transform from this:
@xml = '< RB >< RI Type="Sausages" Term="12" DI="11" PO="0" />< PromoInfo />< /RB >'
...to this...
@xml = '< RB >< RI >< Type >Sausages< /Type >< Term >12 < /Term >< DI >11 < PO >0 < /PO >< /RI >< /RB >'
How can I do this using either OPENXML or some other means in SQL 2005? Basically I'm needing to "unbox" the attributes in the first @xml and create separate nodes out of each of them.
@xml = '< RB >< RI Type="Sausages" Term="12" DI="11" PO="0" />< PromoInfo />< /RB >'
...to this...
@xml = '< RB >< RI >< Type >Sausages< /Type >< Term >12 < /Term >< DI >11 < PO >0 < /PO >< /RI >< /RB >'
How can I do this using either OPENXML or some other means in SQL 2005? Basically I'm needing to "unbox" the attributes in the first @xml and create separate nodes out of each of them.
Separate topics with commas, or by pressing return. Use the delete or backspace key to edit or remove existing topics.
You can leave an optional "tip" with Mahalo's virtual currency, Mahalo Dollars. If you are asking a difficult question that might require some research, or if you'd like a wide variety of feedback, a higher tip often leads to more answers to your question.
M$2 Answers
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
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
You can leave an optional "tip" with Mahalo's virtual currency, Mahalo Dollars. If you are asking a difficult question that might require some research, or if you'd like a wide variety of feedback, a higher tip often leads to more answers to your question.
M$Report Abuse
