help on xml schema

• alan lim

  Newbie

  Points: 1

  More actions

  August 30, 2010 at 12:33 am

  #74135

  <HousingLoan xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  <MemberData>

  <Name>alan</Name>

  </MemberData>

  <FinanciarData>

  <Source>Bank</Source>

  </FinanciarData>

  </HousingLoan>

  Attached with sample XML, anybody can help on select the FinanciarData->Source with double namespace define in the header. Thanks.

• LutzM

  SSC Guru

  Points: 107049

  More actions

  August 30, 2010 at 3:48 am

  #1213642

  You need to include the namespace declaration in your SELECt statement.

  Assuminig you're using SS2K5 or SS2K8, here's the XQuery snippet I would use:

  declare @xml xml

  set @xml='<HousingLoan xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  <MemberData>

  <Name>alan</Name>

  </MemberData>

  <FinanciarData>

  <Source>Bank</Source>

  </FinanciarData>

  </HousingLoan>'

  ;

  WITH XMLNAMESPACES

  ('http://www.w3.org/2001/XMLSchema-instance' as xsi,

  'http://www.w3.org/2001/XMLSchema' as xsd

  )

  SELECT

  c.value('Source[1]','VARCHAR(10)') as source

  FROM

  @xml.nodes('HousingLoan/FinanciarData') T(c)

  ---

  
  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]