September 25, 2008 at 6:59 am
Jim,
You are right. I am not able to download as well.
Let me contact Steve, he might be able to help.
regards
Jacob
.
September 25, 2008 at 7:12 am
I've updated the links. Some of these broke last year when we migrated platforms and these must have been missed.
September 25, 2008 at 7:23 am
Thanks, Steve. That did the trick.
(And thank you again, Jacob.)
February 18, 2009 at 6:11 pm
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.
February 19, 2009 at 5:16 am
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
.
February 19, 2009 at 10:10 am
Hi Jacob,
Thanks a lot for your prompt response. The only problem is I am not a .Net developer and only do database development and administration. One thing that I do want to mention is that performance is not one of the top priority for this process as it will be used to load data into destination tables maybe once/twice a month on demand depending upon if the user needs to change the variable values after the intial load. This will be actually used to load configuration values into our configuration database and has two columns and atmost maybe about hundred rows in the two destination tables with minimum growth overtime.
Yes your are right, I have been trying to play around with the XML data type methods in SQL 2008 and find that it has few limitations on how much I can do, hence wanted to get your advice if it is at all possible to do just with XML data type methods in SQL 2008 and maybe T-SQL wherever needed.
I did look at some .Net libraries including LINQ and see that it has more coverage on handling such task.
But I have no choice at this time to do it using .Net as it will take some time to learn even the basics and I need to complete this task by next week. I will discuss with my team regarding the limitation that I may have doing this in SQL, but it seems I will have it get around it using SQL as the .Net developers in the team are busy with other tasks.
Thank you so much for your feedback. I really appreciate that. Atleast I know from you that it is not impossible using XML data type in SQL through your experience.
December 23, 2011 at 3:39 am
Things are getting better and better. And with the comments on the discussion even better. Last comments and questions from bdba where helpfull too as my final goal is to accomplish something similar to what he was trying to do.
Just in case, here is again the code with comments in spanish. The link to the code on each example works fine 😉
DECLARE @x XML
SET @x = '<orderInfo>
<item code="A001" category="FOOD" subcategory="Candies">
<description>Nestle Munch</description>
<qty>10</qty>
<rate>11.25</rate>
</item>
<item code="A002" category="FOOD" subcategory="Biscuits">
<description>Britania Good Day</description>
<qty>15</qty>
<rate>12.25</rate>
</item>
</orderInfo>'
--la query a continuación lista los valores de los atributos de la variable XML declarada
--los nombres de los atributos los referenciamos poniendo @ delante del mismo
SELECT
x.item.value('@code[1]', 'VARCHAR(20)') AS ItemCode,
x.item.value('@category[1]', 'VARCHAR(20)') AS category,
x.item.value('@subcategory[1]', 'VARCHAR(20)') AS subcategory
FROM @x.nodes('//orderInfo/item') AS x(item)
--ahora queremos los valores de los nodos, para esto no necesitamos @ como en el caso anterior
SELECT
x.item.value('description[1]', 'VARCHAR(20)') AS description,
x.item.value('qty[1]', 'INT') AS qty,
x.item.value('rate[1]', 'FLOAT') AS rate
FROM @x.nodes('//orderInfo/item') as x(item)
--veamos como obtener todos los valores, tanto de los atributos como los nodos y listarlos en una tabla
--notar que usamos @ para referenciar los valores de los atributos como en el primer ejemplo
SELECT
x.item.value('@code[1]', 'VARCHAR(20)') AS ItemCode,
x.item.value('@category[1]', 'VARCHAR(20)') AS category,
x.item.value('@subcategory[1]', 'VARCHAR(20)') AS subcategory,
x.item.value('description[1]', 'VARCHAR(20)') AS description,
x.item.value('qty[1]', 'INT') AS qty,
x.item.value('rate[1]', 'FLOAT') AS rate
FROM @x.nodes('//orderInfo/item') AS x(item)
--pero quizás nos interesa solo una fila, no todas
--veamos diferentes formas de como hacerlo, en este caso solo tomamos la 1ra fila, el valor del campo 'code'
SELECT @x.value('(/orderInfo/item/@code)[1]', 'VARCHAR(20)')
SELECT @x.value('(/orderInfo/item/@code)[1]', 'VARCHAR(20)') AS Code
DECLARE @code VARCHAR(20)
SELECT @code = @x.value('(/orderInfo/item/@code)[1]', 'VARCHAR(20)')
SELECT @code as Code
--si queremos los valores del campo 'code' de la 2da fila
SELECT @x.value('(/orderInfo/item/@code)[2]', 'VARCHAR(20)')
--busquemos ahora el valor de un nodo, no de un atributo
--en este caso 'description' en la 1ra fila, notese que no usamos @ para referenciar el campo
SELECT @x.value('(/orderInfo/item/description)[1]', 'VARCHAR(20)')
--igual pero en la 2da fila
SELECT @x.value('(/orderInfo/item/description)[2]', 'VARCHAR(20)')
--ahora apliquemos un filtro, busquemos los valores solo del item 'A002'
SELECT x.item.value('@code[1]', 'VARCHAR(20)') AS ItemCode,
x.item.value('@subcategory[1]', 'VARCHAR(20)') AS subcategory,
x.item.value('description[1]', 'VARCHAR(20)') AS description,
x.item.value('qty[1]', 'INT') AS qty
FROM @x.nodes('//orderInfo/item') AS x(item)
WHERE x.item.value('@code[1]', 'VARCHAR(20)') = 'A002'
-- y ahora filtremos por el valor de un elemento en los nodos
SELECT x.item.value('@code[1]', 'VARCHAR(20)') AS ItemCode,
x.item.value('@subcategory[1]', 'VARCHAR(20)') AS subcategory,
x.item.value('description[1]', 'VARCHAR(20)') AS description,
x.item.value('qty[1]', 'INT') AS qty
FROM @x.nodes('//orderInfo/item') AS x(item)
WHERE x.item.value('description[1]', 'VARCHAR(20)') = 'Britania Good Day'
April 16, 2019 at 6:23 pm
Does anyone have a valid link to the article. When I try to access it now, it appears to be invalid.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply