How to Generate Required xml format using t-sql

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » How to Generate Required xml format using...

12 posts, Page 1 of 2

1 2 »»

How to Generate Required xml format using t-sql

Rate Topic

Display Mode

Topic Options

Author

Message

ravidas

Posted Tuesday, August 28, 2012 8:37 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:30 PM
Points: 38, Visits: 156

Post #1350991

GSquared

Posted Tuesday, August 28, 2012 9:57 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:55 PM
Points: 15,442, Visits: 9,571

I think you'll need to use FOR XML EXPLICIT in order to do that.

Details: http://msdn.microsoft.com/en-us/library/ms189068.aspx
Example: http://msdn.microsoft.com/en-us/library/bb522438.aspx

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1351068

Matt Miller (#4)

Posted Tuesday, August 28, 2012 3:07 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:20 PM
Points: 6,998, Visits: 13,947

SQLXML wants to actually define the namespace reference. Assuming tha'ts okay - you might care to use a syntax something like:

with xmlnamespaces('http://types.abc.xyz/timestamps/20110315/' as xsi)
select '20120101' as "timevalue/@value",
(select 'sa' as "@xsi:type",
	'10' as "@value"
for XML path('value'), type) 
for xml path('code')

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Post #1351285

oconnor_dp

Posted Tuesday, August 28, 2012 6:43 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 07, 2013 1:08 AM
Points: 5, Visits: 253

You'll need to define the xsi namespace correctly. Try this:

;WITH XMLNAMESPACES( 'http://www.w3.org/2001/XMLSchema-instance' AS xsi )
SELECT
	'20120101' AS "timevalue/@value",
	'sa' AS "value/@xsi:type",
	'10' AS "value/@value"
FOR XML PATH(''), ROOT('code')

Post #1351347

Jeff Moden

Posted Tuesday, August 28, 2012 9:59 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906, Visits: 26,790

I'm not so hot on XML so I need to ask... why do name spaces sometimes have names of websites that have nothing to do with what is being worked on??? this is as bad as CDOSYS and CDONTS.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1351379

Matt Miller (#4)

Posted Tuesday, August 28, 2012 11:00 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:20 PM
Points: 6,998, Visits: 13,947

Since XML is supposed to be an interchange language it's advisable for you to publish how others could speak your language. The namespacing allows you to create and publish your named types to a (web) server, so folks who need to communicate with you can use your "objects" (message types, etc...) to talk to you. Think of it like and SDK for EDI, but self-describing.

So - the web site would point you to a URL which would serve up said schema file. Now most applications don't have anything that lofty in mind (so the URL is only a placeholder), but the mechanism is there "just in case" you'd like to.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Post #1351383

ravidas

Posted Wednesday, August 29, 2012 12:14 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:30 PM
Points: 38, Visits: 156

Thank You all for the reply.

But I am facing a new problem with xmlnamespaces

I am declaring with following code before my long sql query

; WITH XMLNAMESPACES (
'urn:hl3-org:v3/voc' as voc
,'http://www.w3.org/2001/XMLSchema-instance' as xsi
,'urn:hl3-org:v3 CDA.xsd' as schemaLocation)

The problem I am facing is this is the url's repeating all over my xml nodes.But I just need them in the header alone and also I need to add in my namespaces 'urn:hl3-org:v3' AS xmlns like

; WITH XMLNAMESPACES ('urn:hl7-org:v3' AS xmlns
,'urn:hl7-org:v3/voc' as voc
,'http://www.w3.org/2001/XMLSchema-instance' as xsi
,'urn:hl7-org:v3 CDA.xsd' as schemaLocation)

its throwing an error

"Prefix 'xmlns' used in WITH XMLNAMESPACES is reserved and cannot be used as a user-defined prefix"

Can you please Help!!

Thanks & Regards
Ravi

Post #1351407

ravidas

Posted Wednesday, August 29, 2012 1:15 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:30 PM
Points: 38, Visits: 156

To make Furthur clear about my question
I just want to add what exactly i am looking for.

;WITH XMLNAMESPACES(DEFAULT 'urn:hl3-org:v3'
,'urn:hl3-org:v3/voc' AS VOC
,'http://www.w3.org/2001/XMLSchema-instance' AS xsi
, 'urn:hl3-org:v3 CDA.xsd' AS schemaLocation)

These name spaces are repeating in each elements all over my xml.txtPost_CommentEmoticon(' w00t

');

I just wanted to know is there any way to get rid of these namespaces for each element

Post #1351433

Jeff Moden

Posted Wednesday, August 29, 2012 7:20 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906, Visits: 26,790

Matt Miller (#4) (8/28/2012)

That brings to mind the next question. Will the code continue to work if the URL isn't valid?

--Jeff Moden

Post #1351602

GSquared

Posted Wednesday, August 29, 2012 7:40 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:55 PM
Points: 15,442, Visits: 9,571

Jeff Moden (8/29/2012)

Matt Miller (#4) (8/28/2012)

That brings to mind the next question. Will the code continue to work if the URL isn't valid?

Yep. It doesn't actually connect. It just tells a dev working on it where to look for documentation. Try an impossible URL in one to test this. It'll work just fine, mechanically.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1351619

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions