|
Comments
|
|
Great - wanting to see how to parse element-based xml values with xquery. Thanks!
|
|
Plamen Ratchev on
11/26/2008
Jay, this video demonstrates parsing element based XML. If you mean you want to see parsing of attribute based XML, then here is the link to the video:
http://www.jumpstarttv.com/shredding-xml-attributes-with-the-xquery-value-function_528.aspx
|
|
|
blurry video
|
|
Devin Knight on
8/20/2009
Great video Plamen! You made it look easy.
|
|
Greg Goodall on
8/20/2009
clear and simple. Excellent
|
|
|
would like to see an attribute-centric version too
|
|
Sara Karasik on
8/20/2009
Not to be a racist, but I found his accent very hard to follow. the explanations were great, however.
|
|
|
Paul, there is another video that shows the attribute-centric version. Here is the link: http://www.jumpstarttv.com/shredding-xml-attributes-with-the-xquery-value-function_528.aspx
|
|
|
good one!!very helpful
|
|
|
Okay, but could have gexplained why 'T(customer)' was used as an "alias" in the FROM clause.
|
|
|
Ed, the alias for the column is needed in order to reference the column in the SELECT list.
|
|
|
Very simple and easy to understand. Any one could get started with this example. Would like a way to copy the code.
|
|
|
Denzil, all scripts are attached in the References section on the top of this page.
|
|
|
Video was pretty concise. However I would like to know one important thing. As I am new to XML, could somebody explain why we have used brakets () arround the element names. If have to shred the attribute based xml then we simply use @attribute_name and we get the data. For attributes we don't use brakets arround it. Why?
|
|
|
Why do we need to use brakets () if it is a element based xml but not in case of a attribute based xml shredding where we simply type @attribute_name. Sorry if the question is too elementary, but I would appreciate any help on this?
|
|
Plamen Ratchev on
10/14/2009
Rajat, it is required that the value() method returns a singleton atomic value. When you access attributes, the @attribute_name is atomic value. But when you access elements, it is a collection of multiple elements, expressed as (element_name). In order to return atomic value you specify the instance in the collection that you want to return, like (element_name)[1], which returns the first element in the collection.
|
|
|
So you are saying that we can have multiple elements with the same name and that's why we have used the index based access. Thanks a lot for your help. I have modified the example for other readers to demonstrate this...
-- Declare XML variable
DECLARE @data XML;
-- Element-centered XML
SET @data =
N'<data>
<customer>
<id>1</id>
<name>Allied Industries</name>
<name>Allied</name>
</customer>
<customer>
<id>2</id>
<name>Trades International</name>
<name>Trades</name>
</customer>
</data>';
-- Using the value() method
SELECT T.customer.value('(id)[1]', 'INT') AS customer_id,
T.customer.value('(name)[1]', 'VARCHAR(20)') AS customer_name,
T.customer.value('(name)[2]', 'VARCHAR(20)') as customer_name1
FROM @data.nodes('data/customer') AS T(customer);
|
|
|
good stuff
|
|
|
again a short informative video. thanks
|
|
|
Very neat. The method is very intuitive and this lesson will be quite useful.
|
|
|
This will be very helpful. Thank you for sharing.
|
|
|
very good
|
|
|
have xml reading from an outlook email that you can convert?
|
|
Dexter Jones on
1/11/2012
Very clear and concise.
|
|
|
I'm still not clear on the alias. I'm used to "AS something" being an alias so why are you using something in parentheses "T(customer)" and then subsequently using a dotted notation (T.customer)? I'm already using this in my code but that part never seemed clear to me. Is there further functionality hidden in that syntax?
|
|
|
excellent xml shredding demo
|
|
|
Thank you! I have been looking for the method to do exactly this!
|
|
Jack Hellmuth on
1/17/2012
How would you apply this to extracting parameter values for SRS subscriptions.ExtensionSettings? There are multiple elements for ParameterValue. Or is there an easier way to get the same information displayed in Report Manager?
|
|
Steve Harris on
1/20/2012
Excellent example.
|