SQLServerCentral / Discuss Content Posted by Jacob Sebastian / Article Discussions / Article Discussions by Author / Advanced XML Processing / Latest Posts

RE: Advanced XML Processing

yazalpizar_ — Fri, 23 Dec 2011 08:25:37 GMT

[quote][b]yazalpizar_ (12/23/2011)[/b][hr]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?[/quote]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

RE: Advanced XML Processing

yazalpizar_ — Fri, 23 Dec 2011 03:14:10 GMT

In case anyone mayneeded, here is the same post, but with comments in spanish, enjoy[code="sql"]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 XMLSELECT 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 TYPESELECT 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 ELEMENTSSELECT OrderNumber, ItemNumber, Qty FROM OrderDetails FOR XML AUTO, TYPE, ELEMENTS--pero no tenemos un elemento root, añadimos ROOT al finalSELECT 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 renonbrarloSELECT 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 tablaSELECT 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 RAWSELECT 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 XSINILUPDATE OrderDetails SET Qty = NULL WHERE OrderDetailID = 3SELECT OrderNumber,ItemNumber,Qty FROM OrderDetails FOR XML RAW('itemInfo'), TYPE, ELEMENTS XSINIL, ROOT('orderInfo')[/code]

RE: Advanced XML Processing

yazalpizar_ — Fri, 23 Dec 2011 02:30:02 GMT

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?

RE: Advanced XML Processing

Kiat Tang — Fri, 09 Jul 2010 08:19:05 GMT

Thanks!

RE: Advanced XML Processing

jacob sebastian — Fri, 09 Jul 2010 00:56:43 GMT

You will need to build a dynamic query and execute it (to have the value of a column transformed as an element name).

RE: Advanced XML Processing

Kiat Tang — Thu, 08 Jul 2010 16:53:26 GMT

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?

RE: Advanced XML Processing

Anipaul — Mon, 05 May 2008 05:52:26 GMT

Great article!!!! Very simple and well written!!! :)

RE: Advanced XML Processing

jacob sebastian — Fri, 26 Oct 2007 04:11:16 GMT

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

RE: Advanced XML Processing

RyanRandall — Fri, 26 Oct 2007 03:41:39 GMT

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')

RE: Advanced XML Processing

jacob sebastian — Thu, 25 Oct 2007 22:42:17 GMT

[quote][b]flurk118 (10/25/2007)[/b][hr]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 attachedTa[/quote]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'UNIONSELECT 2 AS Tag, 1 AS Parent, NULL, 'Jacob', '401, Time Square', '999', NULL, NULL, NULLUNION SELECT 3 AS Tag, 1 AS Parent, NULL, NULL, NULL, NULL, OrderNumber, ItemNumber, QtyFROM OrderDetailsFOR XML EXPLICIT Both of the above queries give the same output that you asked.

RE: Advanced XML Processing

flurk118 — Thu, 25 Oct 2007 09:17:59 GMT

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 attachedTa

RE: Advanced XML Processing

Paul Manzano — Wed, 08 Aug 2007 09:19:00 GMT

Excellent Job! Very easy to follow!

RE: Advanced XML Processing

d_george — Tue, 26 Jun 2007 07:22:00 GMT

That's great. It helped me out a lot.

Does anyone have any info/styles/code/opinions on how to retrieve on the other end?

Specifically how to parse this file into variables in VB.Net?

RE: Advanced XML Processing

Krupali Mehta — Thu, 17 May 2007 09:27:00 GMT

Its very straightforward. also very practicle.

keep it up......!!!

thanks

RE: Advanced XML Processing

norman harley — Mon, 14 May 2007 23:14:00 GMT

thanks for a well balanced document that gives concise information and syntax to a required information subject

RE: Advanced XML Processing

Suman Punukollu — Thu, 10 May 2007 20:05:00 GMT

nice and simple

RE: Advanced XML Processing

Ivan Weaver — Wed, 09 May 2007 10:36:00 GMT

OH MY GIDDY AUNT!!!

This article is HUGE!!

Resultset > .NET Serialization > direct Object usage

You can autopopulate an objects list properties with a deserializing use of the resultset.

Have a CurrentOrder object? Derialize the resultset in to it. I realize I make it sound simple, but if you are creating a system or can fit in some design tweaks/adjustments to the system, then the ability to autopopulate an Order object directly from a result set without need to 'read' the result and load the properies manually? THAT'S HUGE !!

RE: Advanced XML Processing

stevemc — Wed, 09 May 2007 10:28:00 GMT

Very concise article. I spent a couple days figuring this out. Why is there nothing in BOL as simple as this?

You might note that to assign the results of the xml-generating query to a variable declared as XML, you need to surround the query in parentheses.

declare @xmlparm xml

select @xmlparm = (select orderid from order for xml auto, type)

RE: Advanced XML Processing

jacob sebastian — Wed, 09 May 2007 08:55:00 GMT

Do you have an example that shows your specific problem? I will try to help you out then.

RE: Advanced XML Processing

Adrian Hains — Wed, 09 May 2007 08:26:00 GMT

You can just cast the nvarchar column to xml, but this obviously incurs the overhead of parsing the content and would error out if the column contains invalid xml.use tempdb;create table t (i int, x nvarchar(100));insert t values (1, '<abc>def</abc>');goselect i, cast(x as xml) from t for xml autogo

RE: Advanced XML Processing

noeld — Wed, 09 May 2007 08:02:00 GMT

Great Article! Simple through examples and very clear. Keep'em comming Cheers,

RE: Advanced XML Processing

tbredeme — Wed, 09 May 2007 07:40:00 GMT

Thanks! I needed That

RE: Advanced XML Processing

RyanRandall — Wed, 09 May 2007 07:12:00 GMT

Very nicely-presented article - well done Jacob!

My only suggestion would be to rename this particular article to 'Simple XML Processing', as some people might be unnecessarily scared off

I look forward to the next article...

RE: Advanced XML Processing

Philip Nelson — Wed, 09 May 2007 06:53:00 GMT

Very helpful, thanks. If you have xml already in an nvarchar column, is there any way to have it not be escaped in the for xml output? I'm thinking I might be able to write a query that would insert that nvarchar column into a temp table with an xml datatype. Then I would be able to query inside the xml document itself while joining with other data. Right now we are stuck with LIKE queries.

RE: Advanced XML Processing

Brad Allison — Wed, 09 May 2007 05:59:00 GMT

I agree, that was a great article. Very simple and very worthwhile.

RE: Advanced XML Processing

Rob-242869 — Wed, 09 May 2007 05:06:00 GMT

great article! I never realised how simple it actually is to get XML out of 2005.

RE: Advanced XML Processing

agrinberg — Wed, 09 May 2007 04:52:00 GMT

I found article very usefull. Thank you.

RE: Advanced XML Processing

Vasant Raj — Wed, 09 May 2007 01:26:00 GMT

Nicely done... simple and easy language.

Keep it up!!!

RE: Advanced XML Processing

Ted Cooper-351490 — Tue, 08 May 2007 22:52:00 GMT

Excellent. Now we're cookin' with gas! Thanks.

Advanced XML Processing

jacob sebastian — Wed, 02 May 2007 16:40:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/2982.asp