Advanced XML Processing

Question

Post reply

Advanced XML Processing

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

norman harley Grasshopper Points: 23 More actions · Answer 1

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

Krupali Mehta Valued Member Points: 62 More actions · Answer 2

Its very straightforward. also very practicle.

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

thanks

d_george SSCrazy Points: 2462 More actions · Answer 3

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?

Paul Manzano Newbie Points: 9 More actions · Answer 4

Paul Manzano

Newbie

Points: 9

August 8, 2007 at 9:19 am

#725466

Excellent Job! Very easy to follow!

flurk118 SSC Eights! Points: 820 More actions · Answer 5

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

jacob sebastian SSChampion Points: 11822 More actions · Answer 6

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

UNION

SELECT

3 AS Tag,

1 AS Parent,

NULL,

OrderNumber,

ItemNumber,

Qty

FROM

OrderDetails

FOR XML EXPLICIT

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

.

RyanRandall SSChampion Points: 13623 More actions · Answer 7

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 SSChampion Points: 11822 More actions · Answer 8

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 SSC-Insane Points: 24681 More actions · Answer 9

Great article!!!! Very simple and well written!!! 🙂

Kiat Tang SSChasing Mays Points: 629 More actions · Answer 10

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 SSChampion Points: 11822 More actions · Answer 11

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 SSChasing Mays Points: 629 More actions · Answer 12

Kiat Tang

SSChasing Mays

Points: 629

July 9, 2010 at 8:19 am

#1190819

Thanks!

yazalpizar_ Default port Points: 1425 More actions · Answer 13

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_ Default port Points: 1425 More actions · Answer 14

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_ Default port Points: 1425 More actions · Answer 15

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 😛