Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

XML Workshop - FOR XML PATH Expand / Collapse
Author
Message
Posted Monday, December 5, 2011 2:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 16, 2012 6:44 AM
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/
Post #1216121
Posted Friday, December 23, 2011 5:48 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:23 AM
Points: 142, Visits: 471
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')

Post #1226185
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse