Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


More Advanced XML Processing Examples


More Advanced XML Processing Examples

Author
Message
Jim Russell-390299
Jim Russell-390299
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 1403
I'm learning a lot from this. Thanks!
Not to look a gift horse in the mouth (or publicly reveal my ignorance)...is the raw source code available somewhere? Having to delete the line numbers, and remove the double spacing that happens when you copy from HTML, makes testing your code a bit of a pain. Or does everyone else know the secret?
jacob sebastian
jacob sebastian
SSC-Addicted
SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)

Group: General Forum Members
Points: 472 Visits: 2523
The title of each example that says "example #" is a link. You can click on that to download the source code of each example.

.
Jim Russell-390299
Jim Russell-390299
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 1403
Thanks Jacob. That would make life easier, but when I try it I just get:

Articles with tags Miscellaneous, 2996, axp1, axp2.txt
Search for any content tagged Miscellaneous & 2996 & axp1 & axp2.txt



Sorry, nothing found for this search

Also tried it from a later article where you had "Step #" as links, but got the same "nothing found" result.
jacob sebastian
jacob sebastian
SSC-Addicted
SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)

Group: General Forum Members
Points: 472 Visits: 2523
May be there is a misunderstanding. On the top of each example, (on the left side) there is a hyper link that helps you to download a text file containing the source code. Are you able to locate that? [The title of the hyper link is like Example 1, Example 2 etc.]

.
Jim Russell-390299
Jim Russell-390299
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 1403
Now I really feel dumb. I open the page at:
http://www.sqlservercentral.com/articles/Miscellaneous/2996/

and see:


...
One of the most useful methods exposed by the XML data type is the Value() method. Here, I am presenting 9 more examples which demonstrates the different XML operations that we could perform with the Value() method.

Examples
Example 1

1 /*

2 The following TSQL retrieves attribute values from the XML variable.

3 Attribute names are prefixed with "@".
...

The line "Example 1" is a link, but when I click it, I still get a new page saying:
Articles with tags Miscellaneous, 2996, axp1, axp1.txt
Search for any content tagged Miscellaneous & 2996 & axp1 & axp1.txt



Sorry, nothing found for this search

Am I still looking in the wrong place, or should we just blame Steve Jones :-}
jacob sebastian
jacob sebastian
SSC-Addicted
SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)

Group: General Forum Members
Points: 472 Visits: 2523
Jim,
You are right. I am not able to download as well.

Let me contact Steve, he might be able to help.

regards
Jacob

.
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36234 Visits: 18751
I've updated the links. Some of these broke last year when we migrated platforms and these must have been missed.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Jim Russell-390299
Jim Russell-390299
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 1403
Thanks, Steve. That did the trick.
(And thank you again, Jacob.)
bdba
bdba
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 338
Hi Jacob,

I read all your workshops on XML and have found it very useful. You have presented it very well with simple to advanced examples. Thank you for your efforts and contribution to this topic.

I am working on a task that requires me to update the variable values in one xml using the values for the variable defined in the second XML. I have read on the functionalities provided by the XML data type methods i.e. value(), nodes(), exist(), and modify() but unsure about the extensibility and flexibility on these methods for my task. I was wondering if you could provide some feedback if it is at all possible using XML data type in SQL server or if it will require high level languages such C# or VB. I am ok to use dynamic sql if that would make it possible.

Following I am including my sample XML files that I am trying to do variable replacement on.I am attaching them as a text file as as this does not allow me to paste the xml files. There are three XML files in the attachments.

1. Main XML ( the XMl that needs to be updated using the variable values defined in the Variable XML),
the values inside the {} are variables that needs to be replaced using the values in the Variable XML
2. Variable XML ( the variable XML which has the values for the variables defined in the Main xml).
3. Resulting Main XML after the variables have been replaced with values from Variable XML.

Both these XML will be defined as XML data types and I will ofcourse bound them to a XSD schema collection.
I am planning to use exist() method to check if there are variables in the Main XML that are not defined in the Variable XML. This is an error, vice-versa is Ok.
I am planning to use modify() method to do the replacement of the variables in the Main XML using the values in the variable XML, thus producing the final Main XML.
I will then be using nodes() and value() method to shred the XML and import them to my tables, which I have no problem doing.
I am however seeing limitation for exist() and modify() methods where they require the values to be explicity defined in the method to do variable validation or modification. Any feedback on the extensibility of these methods on acheiving this would be great.
Attachments
SampelXML.txt (2 views, 1.00 KB)
jacob sebastian
jacob sebastian
SSC-Addicted
SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)

Group: General Forum Members
Points: 472 Visits: 2523
Bishal,
I would recommend using .NET XML libraries to perform this, instead of trying to do this in TSQL. If ever you want to do it in TSQL, it would involve running a loop through each element of "Main" XML and then through each attribute and perform an update operation for each value. This will be too complex and will give bad performance.

So, if you have the option to do it in the client application, that should be the first choice.

regards
Jacob

.
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