How to parse the xml using t- sql

• srikant maurya

  Hall of Fame

  Points: 3293

  More actions

  April 23, 2009 at 7:39 am

  #384431

  declare @appro_xml xml ;

  set @appro_xml='

  1020210

  11

  12

  14

  15

  '

  select

  x.col.value('SolicitationID[1]','INT')AS request_id ,

  x.col.value('DeclineReasonCode[1]','varchar(500)')AS DeclineReasonCode

  FROM @appro_xml.nodes('//ScoreAndDataRetrievalRequest') x(col)

  --CROSS APPLY col.nodes('DeclineReasonCode') d(dec)

• srikant maurya

  Hall of Fame

  Points: 3293

  More actions

  April 23, 2009 at 7:43 am

  #982886

  xml file

  declare @appro_xml xml

  set @appro_xml='

  1020

  210

  11

  12

  14

  15

  '

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  April 23, 2009 at 7:44 am

  #982887

  Use the tags [ code="xml" ] and [ /code ] without the spaces 😉

  declare @appro_xml xml ;

  set @appro_xml='

  1020

  210

  11

  12

  14

  15

  '

  select

  x.col.value('SolicitationID[1]','INT')AS request_id ,

  x.col.value('DeclineReasonCode[1]','varchar(500)')AS DeclineReasonCode

  FROM @appro_xml.nodes('//ScoreAndDataRetrievalRequest') x(col)

  --CROSS APPLY col.nodes('DeclineReasonCode') d(dec)

  Greets

  Flo

• srikant maurya

  Hall of Fame

  Points: 3293

  More actions

  April 23, 2009 at 7:44 am

  #982889

  1020

  210

  11

  12

  14

  15

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  April 23, 2009 at 7:45 am

  #982892

  By the way: What's the question?

• GSquared

  SSC Guru

  Points: 260824

  More actions

  April 23, 2009 at 7:46 am

  #982893

  I asked both the same things on his other post, which appears to be pretty much the same junk.

  - 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

• srikant maurya

  Hall of Fame

  Points: 3293

  More actions

  April 23, 2009 at 7:48 am

  #982897

  t-sql

  DeclineReasonCode node

  not returning all node value

  11,12,13 etc

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  April 23, 2009 at 7:52 am

  #982905

  declare @appro_xml xml ;

  set @appro_xml='

  1020

  210

  11

  12

  14

  15

  '

  select

  x.col.value('../../SolicitationID[1]','INT')AS request_id ,

  x.col.value('../../DeclineReasonCode[1]','varchar(500)')AS DeclineReasonCode,

  x.col.value('(./text())[1]', 'varchar(100)') AS DeclineReasonCode

  FROM @appro_xml.nodes('//ScoreAndDataRetrievalRequest/DeclineReasonCode/DeclineReasonCode') x(col)

  --CROSS APPLY col.nodes('DeclineReasonCode') d(dec)

• srikant maurya

  Hall of Fame

  Points: 3293

  More actions

  April 23, 2009 at 8:15 am

  #982938

  My hole xml look like

  declare @appro_xml xml

  set @appro_xml='

  1020

  210

  1001

  24

  19

  11

  12

  14

  15

  1

  0

  1

  1

  1

  0

  0

  0

  Company

  168

  ghfh

  Steve

  Martin

  89082

  28.50

  10835

  1020

  623-17-8923

  John

  Hill

  205008

  71.50

  10835

  1020

  623-22-5433

  Company

  201

  sjkfhjklfhasdkfhsd

  Joseph

  Brenann

  102001

  50.00

  91876

  1020

  233-21-7654

  Curt

  Provenzo

  102344

  50.00

  91876

  1020

  623-14-2314

  Indvidual

  177

  afsjkhf

  Tim Denniston

  544-12-2354

  1

  200345

  Limited

  1611 45 St.

  10 Downing St.

  Brooklyn

  NY

  USA

  11204

  Jason Houseman

  761-33-3354

  1

  150000

  UnLimited

  c/o National Realty and Development Corp.

  3 Manhattanville Road

  Purchase

  PA

  USA

  10577

  Michael J. Prendergast

  623-14-2987

  1

  90000

  Limited

  301 Exchange Boulevard

  Rochester

  NY

  USA

  11204

  '

  select

  --x.col.value('../../SolicitationID[1]','INT')AS request_id ,

  x.col.value('SolicitationID[1]','INT')AS request_id ,

  x.col.value('DeclineReasonCode[1]', 'varchar(100)') AS DeclineReasonCode

  FROM @appro_xml.nodes('//ScoreAndDataRetrievalRequest') x(col)