April 20, 2010 at 12:46 am
Try this:
DECLARE @t TABLE (
Acno VARCHAR(20),
acdesc VARCHAR(20),
actype CHAR(1)
)
INSERT INTO @t (acno, acdesc, actype)
SELECT '1122334', 'Tesr acct', 'C' UNION ALL
SELECT '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>
*/
.
April 20, 2010 at 9:53 am
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 Structure
Crate 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 table
Business_ID Business_name Address City State
12345 ABC St1 Dallas TX
12356 HP ST2 Austin TX
Account 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_Type
12345 111 5678 Corporation C
12345 111 4567 business Checking C
12345 111 7655 Basic Savings S
12356 122 7788 Personal Checking C
12356 122 8899 Checking C
My 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>
<Accounts>
<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>
</Accounts>
<Business_ID> 12356</Business_ID>
<Business_Name> HP </Business_Name>
<Address1>ST2 </Address1>
<City>Austin </City>
<State> TX</State>
<Accounts>
<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>
</Accounts>
</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.
April 20, 2010 at 8:41 pm
try this
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')
.
April 22, 2010 at 8:19 pm
Thanks a lot Jacob, its working..
February 14, 2011 at 9:51 am
Nice Article. The last two workarounds can be achieved by a statement as follows. No need of workarounds
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, root('CustomersByRegion')
Root function gives a way to assign a name for the root element.
December 5, 2011 at 2:17 am
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
December 23, 2011 at 5:48 am
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')
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply