query an xml file !?

• christophe.bernard 47659

  Ten Centuries

  Points: 1169

  More actions

  February 17, 2011 at 7:02 am

  #234557

  Hi all,

  i'm a beginner with t sql ..

  i have a xml file with some attribute and i'd like to get these value ...

  but i can't ..

  here is it my code

  declare @xmlFile xml

  set @xmlFile = '<?xml version="1.0"?>

  <FamilyAndSubFamily>

  <myStruct parentStructId="0" family="18" subFamily="11" />

  <myStruct parentStructId="0" family="43" subFamily="32" />

  <myStruct parentStructId="0" family="7" subFamily="0" />

  </FamilyAndSubFamily>

  '

  --

  SELECT

  convert(int, convert(varchar(max), i.query('parentStructId/text()' ))) as parentNoeudId

  , convert(int, convert(varchar(max), i.query('family/text()' )) ) as familyId

  , convert(int, convert(varchar(max), i.query('subFamily/text()' )) ) as subFamilyId

  FROM @xmlFile.nodes('/FamilyAndSubFamily/myStruct') as x(i)

  the result is :

  0 0 0

  0 0 0

  0 0 0

  and i would lie to have

  0 18 11

  0 43 32

  0 7 0

  any idea ?

  Thanks for all

  Christophe

• GSquared

  SSC Guru

  Points: 260824

  More actions

  February 17, 2011 at 8:24 am

  #1288264

  Try this:

  declare @xmlFile xml

  set @xmlFile = '<?xml version="1.0"?>

  <FamilyAndSubFamily>

  <myStruct parentStructId="0" family="18" subFamily="11" />

  <myStruct parentStructId="0" family="43" subFamily="32" />

  <myStruct parentStructId="0" family="7" subFamily="0" />

  </FamilyAndSubFamily>

  ';

  --

  SELECT

  x.i.query('.').value('(myStruct/@parentStructId)[1]','int') AS ParentStructID,

  x.i.query('.').value('(myStruct/@family)[1]','int') AS Family,

  x.i.query('.').value('(myStruct/@subFamily)[1]','int') AS SubFamily

  FROM @xmlFile.nodes('/FamilyAndSubFamily/myStruct') as x(i);

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• LutzM

  SSC Guru

  Points: 107049

  More actions

  February 17, 2011 at 11:07 am

  #1288409

  I'd rather go with

  SELECT

  x.i.value('(@parentStructId)[1]','int') AS ParentStructID,

  x.i.value('(@family)[1]','int') AS Family,

  x.i.value('(@subFamily)[1]','int') AS SubFamily

  FROM @xmlFile.nodes('/FamilyAndSubFamily/myStruct') as x(i);

  It performs better than the x.i.query('.').value approach. (see execution plan and statistics on a larger data volume)

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• christophe.bernard 47659

  Ten Centuries

  Points: 1169

  More actions

  February 18, 2011 at 12:57 am

  #1288662

  Hi GSquared, LutzM

  thanks for your time and your knowledge ..that's work ..yes 🙂 🙂 !

  I'm a beginner with t sql and i'm not sure to understand your code but when i will be some more time i will try to understand it ..

  Thanks and have a nice day..

  Christophe

• LutzM

  SSC Guru

  Points: 107049

  More actions

  February 18, 2011 at 3:55 am

  #1288728

  christophe.bernard 47659 (2/18/2011)

  ---

  Hi GSquared, LutzM

  thanks for your time and your knowledge ..that's work ..yes 🙂 🙂 !

  I'm a beginner with t sql and i'm not sure to understand your code but when i will be some more time i will try to understand it ..

  Thanks and have a nice day..

  Christophe

  Hi Christophe,

  Glad we could help! 😀

  Whether you're a T-SQL beginner or not, XQuery is a "slightly" different concept and syntax.

  If you want to learn more about XQuery, you might find Jacob Sebastians blog very helpful.

  Regarding T-SQL you already found the best resourse: SSC (aka SQLServerCentral) 🙂

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• GSquared

  SSC Guru

  Points: 260824

  More actions

  February 18, 2011 at 6:57 am

  #1288815

  LutzM (2/17/2011)

  ---

  I'd rather go with

  SELECT

  x.i.value('(@parentStructId)[1]','int') AS ParentStructID,

  x.i.value('(@family)[1]','int') AS Family,

  x.i.value('(@subFamily)[1]','int') AS SubFamily

  FROM @xmlFile.nodes('/FamilyAndSubFamily/myStruct') as x(i);

  It performs better than the x.i.query('.').value approach. (see execution plan and statistics on a larger data volume)

  Aha! I knew I was missing something, but couldn't spot what. I tried to do it without the .query piece, but I was leaving in the myStruct node in the value path, and getting nulls. Thanks.

  (I used to work with XQuery a lot, but haven't done as much with it in the last year or so.)

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• christophe.bernard 47659

  Ten Centuries

  Points: 1169

  More actions

  February 23, 2011 at 1:29 am

  #1290259

  Hi guys,

  thanks ..

  and many thanks for the link i must to keept it in my favorite 🙂

  Christophe