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


Advanced XML Processing


Advanced XML Processing

Author
Message
jacob sebastian
jacob sebastian
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2712 Visits: 2523
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.

.
RyanRandall
RyanRandall
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4223 Visits: 4652
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.
jacob sebastian
jacob sebastian
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2712 Visits: 2523
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

.
Anipaul
Anipaul
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9821 Visits: 1407
Great article!!!! Very simple and well written!!! Smile



Kiat Tang
Kiat Tang
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 384
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?
jacob sebastian
jacob sebastian
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2712 Visits: 2523
You will need to build a dynamic query and execute it (to have the value of a column transformed as an element name).

.
Kiat Tang
Kiat Tang
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 384
Thanks!
yazalpizar_
yazalpizar_
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

Group: General Forum Members
Points: 316 Visits: 626
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?
yazalpizar_
yazalpizar_
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

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


yazalpizar_
yazalpizar_
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

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