Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

More Advanced XML Processing Examples Expand / Collapse
Author
Message
Posted Thursday, February 19, 2009 10:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 9:15 PM
Points: 112, Visits: 319
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.
Post #660555
Posted Friday, December 23, 2011 3:39 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 8:40 AM
Points: 142, Visits: 429
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'

Post #1226150
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse