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

Advanced XML Processing Expand / Collapse
Author
Message
Posted Thursday, October 25, 2007 10:42 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
flurk118 (10/25/2007)
Nice article.

Has anyone got any examples of similar XML output but with header information included.

Following on from Jacobs article and presuming the existence of an OrderHeader table, the output would look something like attached


Ta


A dirty quick way of doing this is as follows:
SELECT CAST(' ' +
(SELECT
'Jacob' AS 'Name',
'401, TIME SQUARE' AS 'Address',
'007' AS 'Code'
FOR XML PATH('OrderHeader')
) +
(SELECT
OrderNumber,
ItemNumber,
Qty
FROM OrderDetails FOR XML AUTO, ELEMENTS
)+
' ' AS XML)


However, a sweet and gentle way of doing this is by using the EXPLICIT keyword.

SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'OrderInfo!1',
NULL AS 'OrderHeader!2!Name!element',
NULL AS 'OrderHeader!2!Address!element',
NULL AS 'OrderHeader!2!Code!element',
NULL AS 'OrderDetails!3!OrderNumber!element',
NULL AS 'OrderDetails!3!ItemNumber!element',
NULL AS 'OrderDetails!3!Qty!element'
UNION
SELECT
2 AS Tag,
1 AS Parent,
NULL,
'Jacob',
'401, Time Square',
'999',
NULL,
NULL,
NULL
UNION
SELECT
3 AS Tag,
1 AS Parent,
NULL,
NULL,
NULL,
NULL,
OrderNumber,
ItemNumber,
Qty
FROM
OrderDetails
FOR XML EXPLICIT

Both of the above queries give the same output that you asked.


.
Post #415213
Posted Friday, October 26, 2007 3:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652
Nice work Jacob.

Here's a 3rd way, this time using XML PATH...

SELECT
OrderHeader.Name AS 'OrderHeader/Name',
OrderHeader.Address AS 'OrderHeader/Address',
OrderHeader.Code AS 'OrderHeader/Code',
OrderDetails.OrderNumber AS 'OrderDetails/OrderNumber',
OrderDetails.ItemNumber AS 'OrderDetails/ItemNumber',
OrderDetails.Qty AS 'OrderDetails/Qty'
FROM
OrderDetails LEFT OUTER JOIN
(SELECT 'Jacob' AS 'Name', '401, TIME SQUARE' AS 'Address', '007' AS 'Code') OrderHeader
ON OrderDetails.ItemNumber = 'A001'
FOR XML PATH (''), ROOT ('OrderInfo')



Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.
Post #415285
Posted Friday, October 26, 2007 4:11 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Excellent!
Ryan's code is much simpler than the two options I presented. This is really good and easy to understand.

one small note: we need to make sure that the OrderHeader will come before OrderDetails. Currently it does. But if the query results try to use an ORDER BY clause, it is possible that the OrderHeader will be placed somewhere in the middle or at the end of the XML structure. But that is not likely to happen often. Just wanted to bring this to notice.

Jacob


.
Post #415290
Posted Monday, May 5, 2008 5:52 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,375, Visits: 1,391
Great article!!!! Very simple and well written!!! :)


Post #494877
Posted Thursday, July 8, 2010 4:53 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: 2 days ago @ 7:39 AM
Points: 113, Visits: 287
Hi, I'm wondering if it's possible to create a xml structure where one column as the element name, another as the element value and another column as the attribute value?
Post #949657
Posted Friday, July 9, 2010 12:56 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
You will need to build a dynamic query and execute it (to have the value of a column transformed as an element name).

.
Post #949753
Posted Friday, July 9, 2010 8:19 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: 2 days ago @ 7:39 AM
Points: 113, Visits: 287
Thanks!
Post #949977
Posted Friday, December 23, 2011 2:30 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 12:40 AM
Points: 142, Visits: 476
I will be forever thankfull for this post. I have gone through all the previous posts about XML handling and this one is exactly what I was looking for. Nevertheless will also take a look on the next posts of the advanced XML processing.

One question, the series of posts "XML Workbench..." jumps from II to IV and then to X. I'm missing some post in the middle? I can't find the III, V,VI,VII,VII and IX posts, maybe just not exists?
Post #1226130
Posted Friday, December 23, 2011 3:14 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 12:40 AM
Points: 142, Visits: 476
In case anyone mayneeded, here is the same post, but with comments in spanish, enjoy

CREATE TABLE [dbo].[OrderDetails](
[OrderDetailID] [int] IDENTITY(1,1) NOT NULL,[OrderNumber] VARCHAR(10) NOT NULL,
[ItemNumber] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Qty] [int] NULL,[Rate] FLOAT NULL,[QtyPicked] INT NULL
) ON [PRIMARY]

INSERT INTO OrderDetails (OrderNumber, ItemNumber, Qty, Rate, QtyPicked)
SELECT '00001', 'A001', 10, 11.25, 0
UNION SELECT '00001', 'A002', 20, 15, 0
UNION SELECT '00001', 'A003', 30, 23.75, 0


--al usar FOR XML AUTO se genera un XML
SELECT OrderNumber, ItemNumber, Qty FROM OrderDetails FOR XML AUTO

--pero el XML generado en realidad es un varchar, nos interesa generar un tipo XML
--para esto usamos TYPE
SELECT OrderNumber, ItemNumber, Qty FROM OrderDetails FOR XML AUTO, TYPE

--por defecto el SQL devuelve los valores en atributos, para añadirlos como tags independientes o nodos usamos ELEMENTS
SELECT OrderNumber, ItemNumber, Qty FROM OrderDetails FOR XML AUTO, TYPE, ELEMENTS

--pero no tenemos un elemento root, añadimos ROOT al final
SELECT OrderNumber, ItemNumber, Qty FROM OrderDetails FOR XML AUTO, TYPE, ELEMENTS, ROOT

--ya tenemos el elemento root, pero tiene como nombre "root", cambiemos el nombre y pongamos "orderInfo"
SELECT OrderNumber, ItemNumber, Qty FROM OrderDetails FOR XML AUTO, TYPE, ELEMENTS, ROOT('orderInfo')

--por defecto los nodos generados tienen de nombre las columnas de la tabla, podemos renonbrarlo
SELECT OrderNumber as 'OrderNum', ItemNumber as ItemCode, Qty as Quantity
FROM OrderDetails FOR XML AUTO, TYPE, ELEMENTS, ROOT('itemInfo')

--ya renombramos el root y las columnas, pero vamos a renombrar lo que vendría siendo cada fila
--para esto usamos la palabra reservada AUTO y un alias para la tabla
SELECT OrderNumber, ItemNumber, Qty
FROM OrderDetails itemInfo FOR XML AUTO, TYPE, ELEMENTS, ROOT('orderInfo')

--pero a veces usar un alias para el nombre de la tabla puede confundirnos en el caso de que la query sea complicada
--usemos la palabra reservada RAW
SELECT OrderNumber, ItemNumber, Qty
FROM OrderDetails FOR XML RAW('itemInfo'), TYPE, ELEMENTS, ROOT('orderInfo')

--por último veremos que pasa con los elementos nulos
--usaremos el modificador
--por defecto el SQL no los carga en el XML, pero si luego este XML lo queremos procesar puede fallarnos
--para que se cargue el elemento null añadimos XSINIL
UPDATE OrderDetails SET Qty = NULL WHERE OrderDetailID = 3
SELECT OrderNumber,ItemNumber,Qty FROM OrderDetails FOR XML RAW('itemInfo'), TYPE, ELEMENTS XSINIL, ROOT('orderInfo')

Post #1226141
Posted Friday, December 23, 2011 8:25 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 12:40 AM
Points: 142, Visits: 476
yazalpizar_ (12/23/2011)
One question, the series of posts "XML Workbench..." jumps from II to IV and then to X. I'm missing some post in the middle? I can't find the III, V,VI,VII,VII and IX posts, maybe just not exists?


I answer myself...I found all the XML series... and surprise, still more than 10 posts to read and test, got some homework for these days
Post #1226273
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse