RE: XML Workshop IV - FOR XML EXPLICIT

Mary.Stephens 54133 — Thu, 26 Jan 2012 14:25:36 GMT

But is there a way to add <?xml version="1.0" encoding="ISO-8859-1" ?> to the resulting xml file

RE: XML Workshop IV - FOR XML EXPLICIT

yazalpizar_ — Fri, 23 Dec 2011 07:52:39 GMT

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 2008R2Again in this post the links to the code are broken.Here is the code with comments in spanish.[code="sql"]--=================================================================================================================================-- 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 CountrySELECT 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 cUNION ALLSELECT 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 cUNION ALLSELECT 2 AS Tag, 1 AS Parent, Country.CountryName, Country.Currency, City.CityName, NULL, NULL, NULLFROM Cities CityINNER JOIN Countries Country ON (Country.CountryID = City.CountryID)UNION ALLSELECT 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 ALLSELECT 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 cUNION ALLSELECT 3 AS Tag, 2 AS Parent, NULL, Country.CountryName, Country.Currency, City.CityName, NULL, NULL, NULLFROM Cities CityINNER JOIN Countries Country ON (Country.CountryID = City.CountryID)UNION ALLSELECT 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.PhoneFROM 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', ParentFOR XML EXPLICIT [/code]

RE: XML Workshop IV - FOR XML EXPLICIT

John Novak-439283 — Thu, 13 Dec 2007 08:51:22 GMT

I don't know if anyone else has already pointed this out, but I was able to add a root node by specifyingFOR 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 cunion allselect 2 as tag, 1 as parent, co.countryname, co.currency, ci.cityname, null, null, nullfrom cities ciinner join countries coon ci.countryID = co.countryidunion allselect 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.phonefrom customers cu inner join cities cion cu.cityid = ci.cityidinner join countries coon ci.countryid = co.countryidorder by 'Country!1!name', 'City!2!name'for xml explicit, root('CustomersByRegion')John

RE: XML Workshop IV - FOR XML EXPLICIT

Mike C — Wed, 08 Aug 2007 09:50:00 GMT

Just a note - FOR XML EXPLICIT is deprecated in Katmai and will be removed in a future version of SQL Server.

RE: XML Workshop IV - FOR XML EXPLICIT

jacob sebastian — Wed, 08 Aug 2007 08:51:00 GMT

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.

RE: XML Workshop IV - FOR XML EXPLICIT

chazmer — Wed, 08 Aug 2007 08:12:00 GMT

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.

RE: XML Workshop IV - FOR XML EXPLICIT

jacob sebastian — Tue, 07 Aug 2007 23:43:00 GMT

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

XML Workshop IV - FOR XML EXPLICIT

jacob sebastian — Thu, 28 Jun 2007 12:27:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/3059.asp

SQLServerCentral / Discuss Content Posted by Jacob Sebastian / Article Discussions / Article Discussions by Author / XML Workshop IV - FOR XML EXPLICIT / Latest Posts

RE: XML Workshop IV - FOR XML EXPLICIT

RE: XML Workshop IV - FOR XML EXPLICIT

RE: XML Workshop IV - FOR XML EXPLICIT

RE: XML Workshop IV - FOR XML EXPLICIT

RE: XML Workshop IV - FOR XML EXPLICIT

RE: XML Workshop IV - FOR XML EXPLICIT

RE: XML Workshop IV - FOR XML EXPLICIT

XML Workshop IV - FOR XML EXPLICIT