FOR XML sub root - 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 2005 Forums

Transact-SQL (2005)

FOR XML sub root

Author

Topic

TiGGi
Starting Member

29 Posts

Posted - 2008-04-06 : 14:09:19

Hi all
I am trying to create xml file from the data, everything works fine cept I can't get the format that I need.

This is my SP:

WITH XMLNAMESPACES (
'urn:http://base.google.com/cns/1.0' as "c"
, 'urn:http://base.google.com/ns/1.0' as "g"
)

SELECT top 2
TITLE "item/title",
description "item/description",
employer "item/g:employer",
job_type "item/g:job_type",
link "item/link",
location "item/g:location",
salary "item/g:salary",
education "item/g:education"

FROM JobExport
FOR XML PATH ('chanel'), ROOT('rss')

I need to get:

<rss version="2.0" xmlns:g="http://base.google.com/ns/1.0" xmlns:c="http://base.google.com/cns/1.0">
- <channel>
- <item>
<title></title>
<description></description>
<g:employer></g:employer>
<g:job_type />
<link></link>
<g:location></g:location>
<g:salary />
<g:education></g:education>
</item>
- <item>
<title></title>
<description></description>
<g:employer></g:employer>
<g:job_type />
<link></link>
<g:location></g:location>
<g:salary />
<g:education>High School</g:education>
</item>
</channel>
</rss>

but I get this:

<rss xmlns:g="urn:http://base.google.com/ns/1.0" xmlns:c="urn:http://base.google.com/cns/1.0">
<chanel>
<item></title>
<description></description>
<g:employer></g:employer>
<g:job_type></g:job_type>
<link></link>
<g:location></g:location>
<g:salary></g:salary>
<g:education></g:education>
</item>
</chanel>
<chanel>
<item>
<title></title>
<description></description>
<g:employer></g:employer>
<g:job_type></g:job_type>
<link></link>
<g:location></g:location>
<g:salary></g:salary>
</item>
</chanel>
</rss>

The problem is that the "chanel" need to be a subroot (not sure what else to call it)

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-04-06 : 19:32:59

I haven't actually tested this but it should be pretty close

WITH XMLNAMESPACES (
'urn:http://base.google.com/cns/1.0' as "c"
, 'urn:http://base.google.com/ns/1.0' as "g"
)
SELECT
(SELECT top 2
TITLE "item/title",
description "item/description",
employer "item/g:employer",
job_type "item/g:job_type",
link "item/link",
location "item/g:location",
salary "item/g:salary",
education "item/g:education"


FROM JobExport
FOR XML PATH('item'), TYPE)
FOR XML PATH('channel') ROOT('rss')

TiGGi
Starting Member

29 Posts

Posted - 2008-04-06 : 20:32:53

Thanx mate, that's very close, the only problem I have right now is that "item" contains xml name spaces. Is there any way to avoid this?
If not is there a way to defined name space prefix 'g' with out having xml name spaces so I can have a field like 'g:location'????

<rss xmlns:g="urn:http://base.google.com/ns/1.0" xmlns:c="urn:http://base.google.com/cns/1.0">
<channel>
<item xmlns:g="urn:http://base.google.com/ns/1.0" xmlns:c="urn:http://base.google.com/cns/1.0">
<title></title>
<description></description>
<g:employer></g:employer>
<g:job_type />
<link></link>
<g:location></g:location>
<g:salary />
<g:education></g:education>
</item>
<item xmlns:g="urn:http://base.google.com/ns/1.0" xmlns:c="urn:http://base.google.com/cns/1.0">
<title></title>
<description></description>
<g:employer></g:employer>
<g:job_type />
<link></link>
<g:location></g:location>
<g:salary />
</item>
</channel>
</rss>

Here's updated sql:

WITH XMLNAMESPACES (
'urn:http://base.google.com/cns/1.0' as "c"
, 'urn:http://base.google.com/ns/1.0' as "g"
)
SELECT
(SELECT top 2
TITLE "title",
description "description",
employer "g:employer",
job_type "g:job_type",
link "link",
location "g:location",
salary "g:salary",
education "g:education"

FROM JobExport
FOR XML PATH('item'), TYPE)
FOR XML PATH('channel'), ROOT('rss')

xatazch
Starting Member

2 Posts

Posted - 2008-07-28 : 03:01:43

i have the same problem is there anyone that know how to solve this?

Here is my code

WITH XMLNAMESPACES('http://URL/AND/SO/ON/' AS ns0)
SELECT
(SELECT SystemName AS "Requester/Contact/UserId",
GRS_ID AS "Requester/Contact/UserOrganisation"
FROM viewCWTGetContactInfo
FOR XML PATH('RequestHead'), TYPE, ELEMENTS),
(SELECT
(SELECT
(SELECT PartyFunction AS "PartyDetail/PartyFunction",
PartyIdentifier AS "PartyDetail/PartyIdentifier",
OrderNo AS "OrderNo",
Amount AS "TotalValue/Amount",
Currency AS "TotalValue/Currency",
DocumentType AS "DocumentType"
FROM viewCWTGetOrderHeadData
FOR XML PATH('OrderHeader'), TYPE, ELEMENTS),
(SELECT OrderItemNo AS "OrderItemNo",
ExpectedDeliveryDate AS "ExpectedDeliveryDate",
AmountOrderLine AS "Price/Amount",
CurrencyOrderLine AS "Price/Currency",
PaymentMethod AS "PaymentMethod",
DueDate AS "DueDate" from viewCWTOrderItem
FOR XML PATH('OrderItem'), TYPE, ELEMENTS)
FOR XML PATH('CheckParameter'), TYPE, ELEMENTS)
FOR XML PATH('RequestData'), TYPE, ELEMENTS)
FOR XML PATH('ns0:ServiceRequest'), TYPE, ELEMENTS

From that i get this
http://screencast.com/t/RW7LK8tV

Here is my problem. what i want is to only have (xmlns:ns0="http://url/AND/SO/ON/") On my Top Element ( This one ns0:ServiceRequest).

Like this

http://screencast.com/t/ZyLPE7JwyNq

If anyone can tell me what i have done wrong i would be grateful

xatazch
Starting Member

2 Posts

Posted - 2008-07-30 : 03:10:32

Hello anyone know how to solve this ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources