Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to Generate Required xml format using t-sql Expand / Collapse
Author
Message
Posted Tuesday, August 28, 2012 8:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, January 04, 2014 5:55 AM
Points: 38, Visits: 158
<code>
<timevalue value="20120101"/>
<value xsi:type="sa" value="10"/>
</code>
Post #1350991
Posted Tuesday, August 28, 2012 9:57 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Posted Tuesday, August 28, 2012 3:07 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:12 PM
Points: 7,084, Visits: 14,685
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
Posted Tuesday, August 28, 2012 6:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, August 28, 2012 9:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1351379
Posted Tuesday, August 28, 2012 11:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:12 PM
Points: 7,084, Visits: 14,685
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
Posted Wednesday, August 29, 2012 12:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, January 04, 2014 5:55 AM
Points: 38, Visits: 158
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
Posted Wednesday, August 29, 2012 1:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, January 04, 2014 5:55 AM
Points: 38, Visits: 158
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('');


I just wanted to know is there any way to get rid of these namespaces for each element
Post #1351433
Posted Wednesday, August 29, 2012 7:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
Matt Miller (#4) (8/28/2012)
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.


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


--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1351602
Posted Wednesday, August 29, 2012 7:40 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
Jeff Moden (8/29/2012)
Matt Miller (#4) (8/28/2012)
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.


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 »

Add to briefcase 12»»

Permissions Expand / Collapse