x

XML Format using the xml datatype in sql server2008

I am trying to generate a format in xml but the format changes in XML. I know that can be done using varchar(max). As I am using methods in xml and methods cannot be called on varchar.So i have to stick with xml datatype. Can anyone help me to generate the file in the below format in XML?

    DECLARE @HoldFile AS XML 
 SELECT @HoldFile=CAST('<?xml version="1.0" encoding="utf-8"?>
 <submission type="SCHOOL" version="4.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="REGISTRATION">
 </submission>' AS XML)
 SELECT @HoldFile

 The submission tag changes to 
 <submission xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" type="SCHOOL" version="4.0" xsi:noNamespaceSchemaLocation="REGISTRATION" />

 I want this tag to be 
      <submission type="SCHOOL" version="4.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="REGISTRATION">
more ▼

asked Nov 01, 2011 at 03:45 PM in Default

avatar image

palum
249 27 29 33

...but the format changes in XML... sorry, I don't understand your question, what is changed..?

Nov 01, 2011 at 04:53 PM JD

the code alphabetizes the attributes, your code that consumes this should not care

Nov 03, 2011 at 01:12 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

From Limitations of the xml Data Type:

The order of attributes in an XML instance is not preserved. When you query the XML instance stored in the xml type column, the order of attributes in the resulting XML may be different from the original XML instance.

So if you put your XML in a XML data type there is no guarantees about the order of attributes.

more ▼

answered Feb 08, 2012 at 01:24 PM

avatar image

Micke
10 1 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x438
x202

asked: Nov 01, 2011 at 03:45 PM

Seen: 1107 times

Last Updated: Feb 08, 2012 at 01:24 PM

Copyright 2017 Redgate Software. Privacy Policy