question

Raj More avatar image
Raj More asked

Converting XML Attribute to Element

In my incoming third-party XML, how do I change the Attribute to be an element? That way, instead of reading this: it will be readable as this: SERVICE_ADDRESS 4320 Acme Rd FunTown FL 33333 I saw snippets about how to use XSLT to do this kind of work. Is it possible to do this using SSIS?
ssissql-server-2008-r2xsdxsltxsl
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jimbobmcgee avatar image
jimbobmcgee answered
Something like the following would work in SQL Server, but I don't think it is the right tool for the job. An XSLT would probably be much better but, since this is a SQL Server Q&A board, let's go with that: DECLARE @xml XML = ' ' SELECT CONTACTTYPE = ROW.x.value('./CONTACTTYPE[1]/@V', 'nvarchar(max)'), ADDRESS1 = ROW.x.value('./ADDRESS1[1]/@V', 'nvarchar(max)'), CITY = ROW.x.value('./CITY[1]/@V', 'nvarchar(max)'), STATE = ROW.x.value('./STATE[1]/@V', 'nvarchar(max)'), ZIP = ROW.x.value('./ZIP[1]/@V', 'nvarchar(max)') FROM @xml.nodes('//ROW') ROW(x) FOR XML PATH('ROW'), ELEMENTS, ROOT('ROWS') As for SSIS, you can do pretty much anything with a .NET Script Task...
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Raj More avatar image Raj More commented ·
@jimbobmcgee I am doing SSIS, so I could use XSLT using an XML task
0 Likes 0 ·
jimbobmcgee avatar image jimbobmcgee commented ·
XSLT is not my forte, so I'll probably bow out here. If I do come up with anything, I'll let you know.
0 Likes 0 ·
Raj More avatar image
Raj More answered
I used XSLT and solved this problem
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.