SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML Workshop IV - FOR XML EXPLICIT


XML Workshop IV - FOR XML EXPLICIT

Author
Message
jacob sebastian
jacob sebastian
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5348 Visits: 2523
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/3059.asp

.
jacob sebastian
jacob sebastian
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5348 Visits: 2523

Hi all,

I see that the source code listing does not wrap lines correctly in IE7. If you happened to find the same, you could try with firefox.

thanks

Jacob



.
chazmer
chazmer
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 32
Great article... but maybe I read it wrong -- I took from the article that FOR XML EXPLICIT is SQL 2005 only, when you could do the same in SQL Server 2000. Nothing huge, but I thought I'd point that out.



jacob sebastian
jacob sebastian
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5348 Visits: 2523

You are correct. The whole XML Workshop focuses on SQL Server 2005 only. That was the reason why I did not mention it.

The rest of the articles in the series are concentrating on XML data type, XQuery, Schemas etc, which are specific to SQL Server 2005 only.



.
Mike C
Mike C
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12024 Visits: 1173
Just a note - FOR XML EXPLICIT is deprecated in Katmai and will be removed in a future version of SQL Server.
John Novak-439283
John Novak-439283
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 14
I don't know if anyone else has already pointed this out, but I was able to add a root node by specifying

FOR XML EXPLICITY, ROOT('CustomersByRegion')

The code below generated fully formed XML:

select
1 as tag,
null as parent,
c.countryname as 'Country!1!name',
c.currency as 'Country!1!currency',
null as 'City!2!name',
null as 'Customer!3!id',
null as 'Customer!3!name',
null as 'Customer!3!phone'
from
countries c

union all

select
2 as tag,
1 as parent,
co.countryname,
co.currency,
ci.cityname,
null,
null,
null
from
cities ci
inner join
countries co
on
ci.countryID = co.countryid

union all

select
3 as tag,
2 as parent,
co.countryname as [name],
co.currency,
ci.cityname as [name],
cu.customernumber as [id],
cu.customername as [name],
cu.phone
from
customers cu
inner join
cities ci
on
cu.cityid = ci.cityid
inner join
countries co
on
ci.countryid = co.countryid
order by
'Country!1!name',
'City!2!name'
for xml explicit, root('CustomersByRegion')

John
yazalpizar_
yazalpizar_
SSChasing Mays
SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)

Group: General Forum Members
Points: 602 Visits: 626
Almost finishing these series of XML workshop, really helped a lot.

The proposal of adding FOR XML EXPLICIT, ROOT('CustomersByRegion') works for me too, I'm using SQL 2008R2

Again in this post the links to the code are broken.

Here is the code with comments in spanish.
--=================================================================================================================================
-- XML Workshop Part IV: usando EXPLICIT
/* EXPLICIT es mucho más complicado de usar que la sintaxis FOR XML (AUTO/RAW/PATH) pero por otra parte permite un control
muy detallado sobre el XML que se quiere generar. Al usar EXPLICIT la query resultante debe tener una estructura
determinada, que se configura mediante varios modificadores en la query misma (TAG, PARENT,...)
*/
--=================================================================================================================================
--vamos a intentar reproducir el mismo XML que usamos en el WorkShop 3
/*
<customersByRegion>
<country name="USA" currency="US Dollars">
<city name="NY">
<customer id="MK" name="John Mark" phone="111-111-1111"/>
<customer id="WS" name="Will Smith" phone="222-222-2222"/>
</city>
<city name="NJ">
<customer id="EN" name="Elizabeth Lincoln" phone="333-333-3333"/>
</city>
</country>
<country name="England" currency="Pound Sterling">
<city name="London">
<customer id="TH" name="Thomas Hardy" phone="444-444-4444"/>
</city>
</country>
<country name="India" currency="Rupees">
<city name="New Delhi">
<customer id="JS" name="Jacob Sebastian" phone="555-555-5555"/>
</city>
</country>
</customersByRegion>
*/

/*
vamos a generar en un primer paso el nodo Country del XML
prestar atención a los modificadore TAG y PARENT.
TAG: esta columna es obligada, informa al generador del XML el nivel del elemento en la jerarquía del XML que queremos obtener
en el ejemplo tenemos "1" que significa que este será el nodo principal del XML
Parent: es la 2da columna obligada. Dice al generador XML cual será el nodo padre del elemento seleccionado.
en el ejemplo es NULL ya que Country no tiene nodo padre
'Country!1!name' : "Country" es el nombre del elemento
"1" especifica el nivel del nodo
"name" es el nombre del atributo
'Country!1!currency' : igual que con Country, tenemos que Country es el nombre del elemento, "1" su nivel y "currency" el atributo

*/
SELECT 1 AS Tag,
NULL AS Parent,
c.CountryName AS 'Country!1!name',
c.Currency AS 'Country!1!currency'
FROM Countries c
/*
veamos que tenemos en el nodo City
prestar atención a:
TAG tiene valor 2, para hacer corresponder la jerarquía en el XML que queremos como resultado
PARENT tiene valor 1 para hacer corresponder con el tag parent superior, que sería Country
*/
SELECT 2 AS Tag,
1 AS Parent,
Country.CountryName,
Country.Currency,
City.CityName
FROM Cities City
INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)

--el resultado lo uniremos con lo que tenemos para el nodo Country
SELECT 1 AS Tag,
NULL AS Parent,
c.CountryName AS 'Country!1!name',
c.Currency AS 'Country!1!currency',
NULL AS 'City!2!name'
FROM Countries c
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
Country.CountryName,
Country.Currency,
City.CityName
FROM Cities City
INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
ORDER BY 'Country!1!name', 'City!2!name'
FOR XML EXPLICIT

/*
so far so good...añadiremos ahora el nodo Customer, siguiendo la misma filosofía de usar el UNION ALL
al igual que antes prestar atención a los valores de TAG y PARENT, 3 y 2 respectivamente para hacer la correspondencia
con el elemento City
*/
SELECT
1 AS Tag,
NULL AS Parent,
c.CountryName AS 'Country!1!name',
c.Currency AS 'Country!1!currency',
NULL AS 'City!2!name',
NULL AS 'Customer!3!id',
NULL AS 'Customer!3!name',
NULL AS 'Customer!3!phone'
FROM
Countries c
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
Country.CountryName,
Country.Currency,
City.CityName,
NULL,
NULL,
NULL
FROM Cities City
INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
UNION ALL
SELECT
3 AS Tag,
2 AS Parent,
Country.CountryName AS [name],
Country.Currency,
City.CityName AS [name],
Customer.CustomerNumber AS [id],
Customer.CustomerName AS [name],
Customer.Phone
FROM
Customers Customer
INNER JOIN Cities City ON (City.CityID = Customer.CityID)
INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
ORDER BY 'Country!1!name', 'City!2!name'
FOR XML EXPLICIT

/*
Aún falta por añadir el elemento root. Para lograrlo añadiremos un elemento padre superior con todos los campos a NULL
y solo el campo TAG=1, modificando todos los demás valores de TAG para tener la correspondencia correcta
*/
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS 'CustomersByRegion!1', -- empty root element
NULL AS 'Country!2!name',
NULL AS 'Country!2!currency',
NULL AS 'City!3!name',
NULL AS 'Customer!4!id',
NULL AS 'Customer!4!name',
NULL AS 'Customer!4!phone'
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL,
c.CountryName AS 'Country!1!name',
c.Currency AS 'Country!1!currency',
NULL AS 'City!2!name',
NULL AS 'Customer!3!id',
NULL AS 'Customer!3!name',
NULL AS 'Customer!3!phone'
FROM
Countries c
UNION ALL
SELECT
3 AS Tag,
2 AS Parent,
NULL,
Country.CountryName,
Country.Currency,
City.CityName,
NULL,
NULL,
NULL
FROM Cities City
INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
UNION ALL
SELECT
4 AS Tag,
3 AS Parent,
NULL,
Country.CountryName AS [name],
Country.Currency,
City.CityName AS [name],
Customer.CustomerNumber AS [id],
Customer.CustomerName AS [name],
Customer.Phone
FROM
Customers Customer
INNER JOIN Cities City ON (City.CityID = Customer.CityID)
INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
ORDER BY 'Country!2!name', 'City!3!name', Parent
FOR XML EXPLICIT


Mary.Stephens 54133
Mary.Stephens 54133
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 66
But is there a way to add <?xml version="1.0" encoding="ISO-8859-1" ?> to the resulting xml file
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search