XML quotation marks - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

XML quotation marks

Author

Topic

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-08-07 : 11:23:52

Select 'DataA' as Column1
,'DataB' as Column2
,'DataC' as Column3
,'DataD' as Column4
for xml path('DIDSRecord'),ROOT ('DIDS'), ELEMENTS XSINIL

the above sql results in :-

<DIDS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >
<DIDSRecord>
<Column1>DataA</Column1>
<Column2>DataB</Column2>
<Column3>DataC</Column3>
<Column4>DataD</Column4>
</DIDSRecord>
</DIDS>

what i would like is the xml to in quotation marks for some columns like so:-

<DIDS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >
<DIDSRecord>
<Column1>"DataA"</Column1>
<Column2>"DataB"</Column2>
<Column3>DataC</Column3>
<Column4>"DataD"</Column4>
</DIDSRecord>
</DIDS>

If possible how to achive this? :-

<DIDS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >
<DIDSRecord>
<Column1 = "DataA"/>
<Column2 = "DataB"/>
<Column3>DataC</Column3>
<Column4 = "DataD"/>
</DIDSRecord>
</DIDS>

Does anybody know how any of the above can done?

Thank you in advance

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-07 : 12:40:08

For the first request:


Select '"DataA"' as Column1
,'"DataB"' as Column2
,'DataC' as Column3
,'"DataD"' as Column4	
for xml path('DIDSRecord'),ROOT ('DIDS'), ELEMENTS XSINIL

The second request is not possible with FOR XML. You're asking for invalid XML!

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-08-08 : 08:51:40

gbritton thank you for the first request.

now are you certian the second request is not possible? its just that i a example lay out which i need to follow exactly and in there some of the columns are the way shown in my second request.

Thank you again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-08 : 09:26:07

This is not a valid XML document


<DIDS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >
<DIDSRecord>
<Column1 = "DataA"/>
<Column2 = "DataB"/>
<Column3>DataC</Column3>
<Column4 = "DataD"/>
</DIDSRecord>
</DIDS>

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-08 : 10:30:31

quote:
Originally posted by Muj9

gbritton thank you for the first request.

now are you certian the second request is not possible? its just that i a example lay out which i need to follow exactly and in there some of the columns are the way shown in my second request.

Thank you again

It is not possible with SQL. You are asking for an invalid XML document.

You can try to validate it here: http://www.liquid-technologies.com/FreeXmlTools/FreeXmlValidator.aspx

and here: http://www.w3schools.com/xml/xml_validator.asp

if you are not convinced.

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-08-08 : 11:08:11

Thank you, i am satisfied with your response. i do have another question , with the xml example file i also have a schema file. what do i need to do with the schema file?

Cheers

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-09-03 : 11:41:16

I did some research and was able to get the result i want. i didn't know what the term was so couldn't really phrase the question properly.

it was attributes i was after all i had to do was to change the alise for examle

select
'column1' as 'Column1/@whatever'
for xml path('Data'),Root('Table')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources