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


XML Workshop - FOR XML PATH


XML Workshop - FOR XML PATH

Author
Message
abhishekonline4all
abhishekonline4all
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 56
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 1
Column name 'DATA()' contains an invalid XML identifier as required by FOR XML; '('(0x0028) is the first character at fault.

SELECT ItemNumber AS 'DATA()'
FROM OrderDetails
FOR XML PATH('')


Regards,
abhIShek Online4all
http://abhishekonline4all.wordpress.com/
yazalpizar_
yazalpizar_
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 626
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


--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', 3

INSERT 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 Countries
SELECT * FROM Cities
SELECT * FROM Customers

--intentemos usando PATH a ver si podemos generar el mismo XML
SELECT 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, CityName
FOR 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 XML
SELECT 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, CityName
FOR 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 AUTO
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

--casi lo tenemos, pero nos falta el elemento ROOT
--con AUTO no hay manera de hacerlo, veamos como mediante un método alternativo
SELECT CAST ('<CustomersByRegion>' + (SELECT
Country.CountryName AS [name],
Country.Currency,
City.CityName AS [name1],
Customer.CustomerNumber AS [id],
Customer.CustomerName AS [name2],
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) + '</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 hacerlo
SELECT 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')


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