Writing Complex XML

Question

Post reply

Writing Complex XML

p.gauci

SSC Veteran

Points: 246
More actions
January 3, 2013 at 3:03 am

#268165

Hi all,
I am trying to write a complex XML using TSQL.
;WITH XMLNAMESPACES('www.your_url.com' as s3,'www.google.com' as s2,'www.google.com.mt' as s1)
SELECT
"Cell"='StringLiteral',
"Cell/Data"='String',
"Cell/Data" = 'Acme, Inc', ''
FOR XML PATH('Row')
<Row xmlns:s1="www.google.com.mt" xmlns:s2="www.google.com" xmlns:s3="www.your_url.com">
<Cell>StringLiteral<Data>StringAcme, Inc</Data></Cell>
</Row>
This works but does not satisfy my requirements. I want to obtain the following result (changes in bold).
<Row xmlns:s1="www.google.com.mt" xmlns:s2="www.google.com" s1:s3="www.your_url.com">
<Cell>StringLiteral<Data>StringAcme, Inc</Data></Cell>
</Row>
Can somebody help me?
Thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

;WITH XMLNAMESPACES('www.your_url.com' as s3,'www.google.com' as s2,'www.google.com.mt' as s1)

select cast(replace(cast((SELECT

"Cell"='StringLiteral',

"Cell/Data"='String',

"Cell/Data" = 'Acme, Inc', ''

FOR XML PATH('Row')) as varchar(8000)),'xmlns:s3','s1:s3') as xml)

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

p.gauci SSC Veteran Points: 246 More actions · Answer 2

Thanks a lot for you response.

I thought about this but it will be a problem for me since the XML i am using is longer than 8000 characters

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

You could try varchar(max) instead.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

p.gauci SSC Veteran Points: 246 More actions · Answer 4

That worked. I thought that 8000 was the limit for a varchar variable.

Thanks a lot and happy new year