August 18, 2009 at 2:03 pm
I have a column in a table with ntext datatype contains xml,
I want to use xquery to shred xml to rows.... Can any one help ?
The XML looks like this in the table coulmn....
August 18, 2009 at 4:15 pm
Would something like this meet your requirement?
;WITH
cte AS (
SELECT id, cast (data AS xml) AS xml FROM #t
)
SELECT id,
x.od.value('batch[1]','varchar(10)') AS batch,
x.od.value('cardexpirationdate[1]','VARCHAR(50)') AS cardexpirationdate,
y.c.value ('merchantRefCode[1]','VARCHAR(50)') AS merchantRefCode
FROM cte
CROSS APPLY xml.nodes('payment') x(od)
CROSS apply od.nodes('replyMessage') y(c)
/* result set
idbatchcardexpirationdatemerchantRefCode
1a01/07/2010621
*/
August 18, 2009 at 4:21 pm
Thanks a lot.... But could you plz let me know wht is cte "after with" and what is x.od.value i mean wht is x.od ?
August 18, 2009 at 5:18 pm
the cte (AKA as CommonTableExpression) basically is a subquery / derived table and could also be written as
SELECT id,
x.od.value('batch[1]','varchar(10)') AS batch,
x.od.value('cardexpirationdate[1]','VARCHAR(50)') AS cardexpirationdate,
y.c.value ('merchantRefCode[1]','VARCHAR(50)') AS merchantRefCode
FROM (SELECT id, cast (data AS xml) AS xml FROM #t
) cte
CROSS APPLY xml.nodes('payment') x(od)
CROSS apply od.nodes('replyMessage') y(c)
The reason why I tend to use CTE's over subqueries / derived tables is readability as well as the option of re-referencing a subquery. As far as performance goes it seems like CTE's are at least as fast as derived tables, so there's no harm to it...
When extracting XML data using XQuery and the nodes() method the result will be stored in a format that can be considered as a table (containing all the nodes) with a column (containing the context of every single node in one row). For details please see http://msdn.microsoft.com/en-us/library/ms188282.aspx%5B/url%5D%5B/b%5D.
In the example above "x" is used as a synonym for holding all payment information in a "table" "x" with a xml "column" "od".
This "table" is used to extract the xml structure from the subsequent "replyMessage" node into another "table" "y", "column" "c".
If you look at the (outer) SELECT statement you'll see that the table and column alias are used to reference the related xml structure in the form of [Table].[Column].value.
For details on how to work with XML data I recommend to look into J. Sebastians "XML Workshop..." articles.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply