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

RE: XML Workshop - FOR XML PATH

yazalpizar_ — Fri, 23 Dec 2011 05:48:17 GMT

The use of PATH is amazing.Just as on previous post of this serie, here is the code with comments in spanish.PS: the links to the code files are broken, maybe where not corrected as on previous posts[code="sql"]--usando PATH se pueden crear jerarquías en los nodos XML--la creacion de los nodos la controlamos mediante alias en las columnas, en este caso: <item> contendrá ItemNumber y Quantity--notar el uso de '/' dentro del nombre de la columna que es el que controla la creación del XML en definitiva SELECT OrderNumber AS 'orderNumber', ItemNumber AS 'item/itemNumber', Qty AS 'item/Quantity' FROM OrderDetails FOR XML PATH('orderInfo'), TYPE, ELEMENTS, ROOT('order') --a veces simplemente queremos una lista de valores, PATH también sirve para esto, aqui devolvemos la lista de los ItemNumbers de la tabla Order SELECT ItemNumber AS 'data()' FROM OrderDetails FOR XML PATH('') --la query anterior devuelve la lista usando espacio como separador, pero seguramente una ',' será mejor SELECT ',' + ItemNumber AS 'data()' FROM OrderDetails FOR XML PATH('') --pero en el ejemplo anterior tenemos una coma al inicio de la cadena, no nos sirve, tenemos que quitarla --el comando STUFF nos ayudará --de hecho esta manera de concatenar valores resulta mucho más rápida que el método normal usando querys, ver: --http://blogs.conchango.com/jamiethomson/archive/2007/04/05/T_2D00_SQL_3A00_-A-T_2D00_SQL-Poser--_2D00_--Part-3.aspx SELECT STUFF((SELECT ',' + ItemNumber AS 'data()' FROM OrderDetails FOR XML PATH('')),1,1,'') --como hemos visto PATH provee una buena herramienta para crar los XML manipulando los alias de las columnas--la mayor parte de las veces PATH será suficiente pero a veces necesitarmos EXPLICIT que nos dará aún más control, pero su uso es mas complicado--veamos como podemos usar diferentes modos del comando XML usando AUTO,RAW y PATH para crear un fichero XML como el siguiente/*<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>*/CREATE TABLE Countries (CountryID INT, CountryName VARCHAR(20), Currency VARCHAR(20)) CREATE TABLE Cities (CityID INT, CityName VARCHAR(20), CountryID INT) CREATE TABLE Customers (CustomerNumber VARCHAR(2), CustomerName VARCHAR(40), Phone VARCHAR(20), CityID INT) INSERT INTO Countries(CountryID, CountryName, Currency) SELECT 1 AS CountryID, 'USA' AS CountryName, 'US Dollars' as Currency UNION SELECT 2, 'England', 'Pound Sterling' UNION SELECT 3, 'India', 'Rupee' INSERT INTO Cities(CityID, CityName, CountryID) SELECT 1 AS CityID, 'NY' AS CityName, 1 AS CountryID UNION SELECT 2, 'NJ', 1 UNION SELECT 3, 'London', 2 UNION SELECT 4, 'New Delhi', 3INSERT INTO Customers(CustomerNumber, CustomerName, Phone, CityID) SELECT 'MK' AS CustomerNumber, 'John Mark' AS CustomerName, '111-111-1111' AS Phone, 1 AS CityID UNION SELECT 'WS', 'Will Smith', '222-222-2222', 1 UNION SELECT 'EN', 'Elizabeth Lincoln', '333-333-3333', 2 UNION SELECT 'TH', 'Thomas Hardy', '444-444-4444', 3 UNION SELECT 'JS', 'Jacob Sebastian', '555-555-5555', 4 SELECT * FROM CountriesSELECT * FROM CitiesSELECT * FROM Customers--intentemos usando PATH a ver si podemos generar el mismo XMLSELECT Country.CountryName AS 'country/name', Country.Currency AS 'country/currency', City.CityName AS 'country/city/name', Customer.CustomerNumber AS 'country/city/customer/id', Customer.CustomerName AS 'country/city/customer/name', Customer.Phone AS 'country/city/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 CountryName, CityNameFOR XML PATH--si miramos el resultado vemos que no es correcto, los valores los queremos tener como atributos, no como nodos en el XML--modifiequemos el PATH y vamos a añadir un ROOT para nombrar la raíz del XMLSELECT Country.CountryName AS 'country/@name', Country.Currency AS 'country/@currency', City.CityName AS 'country/city/@name', Customer.CustomerNumber AS 'country/city/customer/@id', Customer.CustomerName AS 'country/city/customer/@name', Customer.Phone AS 'country/city/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 CountryName, CityNameFOR XML PATH(''), ROOT('CustomersByRegion')--ya casi tenemos lo que queremos, pero si miramos el resultado vemos que no tenemos los customer agrupados por país--usemos ahora el modificador AUTOSELECT 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 CountryName, CityNameFOR XML AUTO--casi lo tenemos, pero nos falta el elemento ROOT--con AUTO no hay manera de hacerlo, veamos como mediante un método alternativoSELECT CAST ('<CustomersByRegion>' + (SELECT Country.CountryName AS [name], Country.Currency, City.CityName AS [name1], Customer.CustomerNumber AS [id], Customer.CustomerName AS [name2], Customer.PhoneFROM Customers Customer INNER JOIN Cities City ON (City.CityID = Customer.CityID) INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)ORDER BY CountryName, CityNameFOR XML AUTO) + '</CustomersByRegion>' AS XML)--ya lo tenemos, pero veamos como hacerlo mediante el modificador RAW--RAW no tiene manera de generar la jerarquía de los nodos XML pero combinandolo con AUTO si podemos hacerloSELECT CAST((SELECT 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 CountryName, CityName FOR XML AUTO) AS XML)FOR XML RAW('CustomersByRegion')[/code]

RE: XML Workshop - FOR XML PATH

abhishekonline4all — Mon, 05 Dec 2011 02:17:01 GMT

Hi,I am new to XML, When i execute the queries ,I replace "data()" with "DATA()",then am getting below error. what is the reason behind this.Msg 6850, Level 16, State 1, Line 1Column name 'DATA()' contains an invalid XML identifier as required by FOR XML; '('(0x0028) is the first character at fault.SELECT ItemNumber AS 'DATA()'FROM OrderDetailsFOR XML PATH('')Regards,abhIShek Online4all[url]http://abhishekonline4all.wordpress.com/[/url]

RE: XML Workshop - FOR XML PATH

Samy-366479 — Mon, 14 Feb 2011 09:51:44 GMT

Nice Article. The last two workarounds can be achieved by a statement as follows. No need of workaroundsSELECT 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 CountryName, CityName FOR XML AUTO, root('CustomersByRegion')Root function gives a way to assign a name for the root element.

RE: XML Workshop - FOR XML PATH

Sri.. — Thu, 22 Apr 2010 20:19:12 GMT

Thanks a lot Jacob, its working..

RE: XML Workshop - FOR XML PATH

jacob sebastian — Tue, 20 Apr 2010 20:41:09 GMT

try this[code]SELECT ( select *, ( select * from account a where a.business_id = b.business_id for xml path('account'), root('accounts'), type ) from business b for xml path(''), root('Small_Business'),TYPE)FOR XML PATH('Business_Type'), ROOT('XMl')[/code]

RE: XML Workshop - FOR XML PATH

Sri.. — Tue, 20 Apr 2010 09:53:00 GMT

Jacob, Sorry for not giving all the details. Here it is.The business information is in one table and Account info is in second table.I need to join these two tables to get the result.Business table StructureCrate table Business(Business_ID varchar(50),Company Name varchar(50),Address_Line1 varchar(50),City varchar(50),State char(2))Let say the data looks like this in this tableBusiness_ID Business_name Address City State12345 ABC St1 Dallas TX12356 HP ST2 Austin TXAccount Table Structure(Business_ID varchar(50),Account_ID Varchar(50),Account_Number varchar(50),Description Varchar(50),Account_Type char(1))Business_ID Account_ID Account_Number Description Account_Type12345 111 5678 Corporation C12345 111 4567 business Checking C12345 111 7655 Basic Savings S12356 122 7788 Personal Checking C12356 122 8899 Checking CMy XML should look like this<XMl xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Business_Type> <Small_Business> <Business_ID> 12345 </Business_ID> <Business_Name> ABC </Business_Name> <Address1> St1 </Address1> <City> Dallas </City> <State> TX </State> [b]<Accounts> [/b] <Account> <Account_Number>5678 </Account_Number> <Description> Corporation<Description> <Account_Type> C</Account_Type> </Account> <Account> <Account_Number> 4567</Account_Number> <Description> business Checking<Description> <Account_Type> C</Account_Type> </Account> <Account> <Account_Number> 7655</Account_Number> <Description>Basic Savings <Description> <Account_Type>S </Account_Type> </Account> [b]</Accounts>[/b] <Business_ID> 12356</Business_ID> <Business_Name> HP </Business_Name> <Address1>ST2 </Address1> <City>Austin </City> <State> TX</State> [b]<Accounts> [/b] <Account> <Account_Number>7788</Account_Number> <Description>Personal Checking <Description> <Account_Type>C </Account_Type> </Account> <Account> <Account_Number>8899 </Account_Number> <Description>Checking <Description> <Account_Type> C</Account_Type> </Account> [b]</Accounts>[/b] </Small_Business> <Business_Type> </XMl>Here the complex elements are BusinessType, Small_Business and Accounts . I am having difficulties to have these elements in my XML.Jacob, This is only sample of my whole XML. I have got a Very Complex XSD with many complex and simple elements. I have Data in 10 tables that i need to pull information from, which means i should use 10 joins. I need to generate an XML from these tables. So i was just trying to see if it is possible in SQL Server.

RE: XML Workshop - FOR XML PATH

jacob sebastian — Tue, 20 Apr 2010 00:46:43 GMT

Try this:[code]DECLARE @t TABLE ( Acno VARCHAR(20), acdesc VARCHAR(20), actype CHAR(1))INSERT INTO @t (acno, acdesc, actype) SELECT '1122334', 'Tesr acct', 'C' UNION ALLSELECT '0004455', 'Bank Test Account', 'S'SELECT '12345' AS Business_ID, ( SELECT acno AS ACCOUNT_NUMBER, acdesc AS ACCOUNT_DESCRIPTION, actype AS ACCOUNT_TYPE FROM @t FOR XML PATH('Account'),ROOT('Accounts'), TYPE )FOR XML PATH(''), ROOT('Business')/*<Business> <Business_ID>12345</Business_Id> <Accounts> <Account> <ACCOUNT_NUMBER>1122334</ACCOUNT_NUMBER> <ACCOUNT_DESCRIPTION>Tesr acct</ACCOUNT_DESCRIPTION> <ACCOUNT_TYPE>C</ACCOUNT_TYPE> </Account> <Account> <ACCOUNT_NUMBER>0004455</ACCOUNT_NUMBER> <ACCOUNT_DESCRIPTION>Bank Test Account</ACCOUNT_DESCRIPTION> <ACCOUNT_TYPE>S</ACCOUNT_TYPE> </Account> </Accounts></Business>*/[/code]

RE: XML Workshop - FOR XML PATH

Sri.. — Mon, 19 Apr 2010 10:43:05 GMT

Hi Jacob, How to add a complex element in XML. For ex: Let say i have my XML like this <Business><Business_ID>12345</Business_Id> <Account> <ACCOUNT_NUMBER>1122334</ACCOUNT_NUMBER> <ACCOUNT_DESCRIPTION>Tesr acct</ACCOUNT_DESCRIPTION> <ACCOUNT_TYPE>C</ACCOUNT_TYPE> </Account> <Account> <ACCOUNT_NUMBER>0004455</ACCOUNT_NUMBER> <ACCOUNT_DESCRIPTION>Bank Test Account</ACCOUNT_DESCRIPTION> <ACCOUNT_TYPE>S</ACCOUNT_TYPE> </Account></Business>And i want the result to look like this<Business><Business_ID>12345</Business_Id><[b]Accounts[/b]> <Account> <ACCOUNT_NUMBER>1122334</ACCOUNT_NUMBER> <ACCOUNT_DESCRIPTION>Tesr acct</ACCOUNT_DESCRIPTION> <ACCOUNT_TYPE>C</ACCOUNT_TYPE> </Account> <Account> <ACCOUNT_NUMBER>0004455</ACCOUNT_NUMBER> <ACCOUNT_DESCRIPTION>Bank Test Account</ACCOUNT_DESCRIPTION> <ACCOUNT_TYPE>S</ACCOUNT_TYPE> </Account></[b]Accounts[/b]></Business>I have added here complex type Accounts. Can you tell me how to change my T sql to add this complex element.

RE: XML Workshop - FOR XML PATH

Doug H-449688 — Thu, 20 Mar 2008 10:51:25 GMT

In case you are still wondering about this, you can use the "WITH XMLNAMESPACES" clause immediately prior to the SELECT clause, and you can add as many namespaces as you need. An example would be: WITH XMLNAMESPACES ('http://www.mynamespaceurl2.com' as "myprefix2", 'http://www.mynamespaceurl1.com' as "myprefix1", DEFAULT 'http://www.mydefaultnamespaceurl.com')SELECT...You then build the select and sub-select statements, using the prefixes you establish in your namespace clause in the AS clause of each field specification, such as "SELECT Field1 AS 'myprefix1:Field1'" and so on.Hope this helps.

RE: XML Workshop - FOR XML PATH

jacob sebastian — Fri, 21 Sep 2007 23:42:00 GMT

Ryan,

run this query:

(excuse the bad formatting)

WITH XMLNAMESPACES (

DEFAULT 'http://www.namespace.net/yourschema.xsd'

)

SELECT 'compensation control ID' AS 'compensatingcontrol/id' ,

'compensation control name' AS 'compensatingcontrol/name' ,

NULL AS 'compensatingcontrol/description' ,

NULL AS 'compensatingcontrol/version' ,

'START' AS 'compensatingcontrol/source' ,

'compensation control description' AS 'compensatingcontrol/controltext' ,

'true' as 'compensatingcontrol/status',

'2007-01-01' AS 'compensatingcontrol/validfrom' ,

'neverexpires' AS 'compensatingcontrol/expirationflag' ,

'1900-01-01' AS 'compensatingcontrol/expiration' ,

'GRMalone' AS 'compensatingcontrol/lastupdatedby' ,

'2007-06-01' AS 'compensatingcontrol/lastupdatedon' ,

'GRMalone' AS 'compensatingcontrol/createdby' ,

'2007-01-01' AS 'compensatingcontrol/createdon' ,

(SELECT 'this is a note'

FOR XML PATH('notes'), TYPE

) as 'compensatingcontrol/additionalinfo',

--NULL AS 'c:compensatingcontrol/additionalinfo/notes' ,

'empnum' AS 'compensatingcontrol/ownerinfo/profile/id' ,

'empusername' AS 'compensatingcontrol/ownerinfo/profile/profilename'

FOR XML PATH('') , TYPE , ELEMENTS XSINIL , ROOT('compensatingcontrols');

this will give the following results:

<id>compensation control ID</id>

<name>compensation control name</name>

<source>START</source>

<controltext>compensation control description</controltext>

<expirationflag>neverexpires</expirationflag>

<lastupdatedby>GRMalone</lastupdatedby>

<createdby>GRMalone</createdby>

</additionalinfo>

<id>empnum</id>

<profilename>empusername</profilename>

</profile>

</ownerinfo>

</compensatingcontrol>

</compensatingcontrols>

RE: XML Workshop - FOR XML PATH

panesofglass — Fri, 21 Sep 2007 06:48:00 GMT

I'm trying to reproduce something like the following:

<compensatingcontrols xmlns ="http://www.namespace.net/schema.xsd">  <compensatingcontrol>    <id>4</id>    <name>Control1</name>    <description />    <version />    <source>Source Name</source>    <controltext>Some control text.</controltext>    <status>enabled</status>    <validfrom>2007-08-03</validfrom>    <expirationflag>neverexpires</expirationflag>    <expiration>1900-01-01</expiration>    <lastupdatedby>OWNER</lastupdatedby>    <lastupdatedon>2007-09-17</lastupdatedon>    <createdby>OWNER</createdby>    <createdon>2007-08-03</createdon>    <additionalinfo>      <notes xmlns="http://www.namespace.net/schema.xsd" />    </additionalinfo>    <ownerinfo>      <profile>        <id>3</id>        <profilename>OWNER</profilename>      </profile>    </ownerinfo>  </compensatingcontrol></compensatingcontrols>

I can produce everything but the root @xmlns and the notes @xmlns attributes using the following query:

SELECT compensatingcontrol.ControlID AS 'compensatingcontrol/id' , compensatingcontrol.[Control Name] AS 'compensatingcontrol/name' , NULL AS 'compensatingcontrol/description' , NULL AS 'compensatingcontrol/version' , 'START' AS 'compensatingcontrol/source' , compensatingcontrol.[Control Description] AS 'compensatingcontrol/controltext' , 'compensatingcontrol/status' =   CASE compensatingcontrol.[Current?]   WHEN 'True' THEN 'enabled'   ELSE 'disabled'  END , '2007-01-01' AS 'compensatingcontrol/validfrom' , 'neverexpires' AS 'compensatingcontrol/expirationflag' , '1900-01-01' AS 'compensatingcontrol/expiration' , 'GRMalone' AS 'compensatingcontrol/lastupdatedby' , '2007-06-01' AS 'compensatingcontrol/lastupdatedon' , 'GRMalone' AS 'compensatingcontrol/createdby' , '2007-01-01' AS 'compensatingcontrol/createdon' , NULL AS 'compensatingcontrol/additionalinfo/notes' , profile.EmpNum AS 'compensatingcontrol/ownerinfo/profile/id' , profile.EmpUserName AS 'compensatingcontrol/ownerinfo/profile/profilename'FROM dbo.vwControls compensatingcontrol LEFT OUTER JOIN dbo.ProcessControl ownerinfo ON compensatingcontrol.ControlID = ownerinfo.ControlID LEFT OUTER JOIN dbo.vwProcessOwners [profile] ON ownerinfo.ProcessID = [profile].ProcessIDFOR XML PATH('') , TYPE , ELEMENTS XSINIL , ROOT('compensatingcontrols');

Thanks for your help! These namespaces are really giving me grief.

RE: XML Workshop - FOR XML PATH

jacob sebastian — Thu, 20 Sep 2007 23:07:00 GMT

Hi Ryan,

please post a sample xml structure that you need. I will then try to write a query for that.

RE: XML Workshop - FOR XML PATH

panesofglass — Thu, 20 Sep 2007 15:15:00 GMT

This is very good information. One other thing I often need, though is to supply namespace declarations in the root or other nodes. How would you add these? I noticed the XSINIL parameter on ELEMENTS adds an XMLSchema-instance namespace.

RE: XML Workshop - FOR XML PATH

jacob sebastian — Wed, 20 Jun 2007 23:53:00 GMT

Hi charles,

There is an article on EXPLICIT already on the way. It may be appearing in the next few weeks.

thankx

Jacob

RE: XML Workshop - FOR XML PATH

charles evans — Wed, 20 Jun 2007 15:07:00 GMT

I think PATH is really a great alternative to doing some simple nesting versus EXPLICIT. I'd like to see an article on PATH vs EXPLICIT to see where PATH can be used to supplant EXPLICIT. Good article.

RE: XML Workshop - FOR XML PATH

jacob sebastian — Wed, 20 Jun 2007 03:07:00 GMT

Hi phil,

Thank you for the comments. I am a regular reader of your articles at simple-talk. They are simply excellent :-).

regards

Jacob

RE: XML Workshop - FOR XML PATH

Phil Factor — Wed, 20 Jun 2007 02:53:00 GMT

This is very useful, Jacob, and nicely explained. Thanks for that. I loved the format. I find PATH the most intuitive way of generating XML to a precise format. I really struggled before I started using it.

p.s. microsoft.public.sqlserver.xml can be really useful for people like me who are still slightly mystified by aspects of XML.

RE: XML Workshop - FOR XML PATH

jacob sebastian — Wed, 20 Jun 2007 01:19:00 GMT

Hi David,

You are right. Thakx for pointing this.

Jacob

RE: XML Workshop - FOR XML PATH

David McKinney — Wed, 20 Jun 2007 01:03:00 GMT

Hi,

Nice article.

At one stage you say that auto mode does not allow you to specify a root element, and you provide a work around. Are you perhaps talking about SQL 2000? As by using the following construct with SQL 2005, you can very simply provide a root element.

FOR XML AUTO, ROOT ('CustomersByRegion')

Regards,

David McKinney.

RE: XML Workshop - FOR XML PATH

Intekhab Sheikh — Wed, 20 Jun 2007 00:34:00 GMT

Very very informative and good material for explanation..Very good

RE: XML Workshop - FOR XML PATH

Hemant Chandurkar-412762 — Tue, 19 Jun 2007 22:33:00 GMT

I like this. Very cool and useful.

XML Workshop - FOR XML PATH

jacob sebastian — Tue, 22 May 2007 10:55:00 GMT

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