February 25, 2010 at 5:28 pm
hello, could you please help with the following example:
DECLARE @doc xml
SET @doc = '<?xml version="1.0" ?>
<produits>
<fournisseurs>
<name>fournisseur1</name>
</fournisseurs>
<produit>
<libelle>produit1</libelle>
<nb>1</nb>
<prix>2</prix>
</produit>
<produit>
<libelle>produit2</libelle>
<nb>2</nb>
<prix>16</prix>
</produit>
<produit>
<libelle>produit3</libelle>
<nb>10</nb>
<prix>3</prix>
</produit>
</produits>
'
SELECT Node.value('(//produit/libelle)[1]', 'varchar(50)') AS mylibelle ,
Node2.value('name[1]', 'varchar(15)') AS myname
FROM @doc.nodes('produits') TempXML (Node)
CROSS APPLY TempXML.Node.nodes('//fournisseurs') AS TempXML2(Node2)
Actual result
produit1fournisseur1
Expected result
produit1fournisseur1
produit2fournisseur1
produit3fournisseur1
Thank you
February 25, 2010 at 6:33 pm
Thank you, I ve just found my mistakes, the solution would be:
SELECT TempXML.Node.value('(libelle)[1]', 'varchar(50)') AS mylibelle,
TempXML2.Node2.value('name[1]', 'varchar(15)') AS myname
FROM @doc.nodes('//produit') TempXML (Node)
CROSS APPLY TempXML.Node.nodes('//fournisseurs') AS TempXML2(Node2)
February 27, 2010 at 11:45 pm
Thanks for letting us know the solution, Apijena.
One suggestion: something that I learned from Paul White, is that explicitly using the "/text()" function for queries like these can have a big performance improvement:
SELECT TempXML.Node.value('(libelle/text())[1]', 'varchar(50)') AS mylibelle,
TempXML2.Node2.value('(name/text())[1]', 'varchar(15)') AS myname
FROM @doc.nodes('//produit') TempXML (Node)
CROSS APPLY TempXML.Node.nodes('//fournisseurs') AS TempXML2(Node2)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply