? on XML Data Parsing....

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  July 9, 2021 at 7:53 pm

  #3906245

  Hi

  I ran into an issue parsing xml,

  Here is a sample

  <catalog issue="spring" date="2015-04-15">

  <book id="bk101">

  <title>Some Great Book</title>

  <genre>Great Books</genre>

  <author>Jon Smith</author>

  <publish_date>2001-12-28</publish_date>

  <price>23.39</price>

  <description>This is a great book!</description>

  </book>

  <cd id="cd101">

  <genre>Sad</genre>

  <artist>Emo Jones</artist>

  <price>15.25</price>

  <description>This music is so sad!</description>

  </cd>

  <map id="map101">

  <title>Good CD</title>

  <location>North America</location>

  <author>Joey Bagadonuts</author>

  <publish_date>2013-02-02</publish_date>

  <price>102.95</price>

  <description>Trail map of North America</description>

  </map>

  </catalog>

  Was using  data.value('(/Catalog/book/title)[1]', 'Varchar(max)') AS Title....

  data.value('(/Catalog/book/publish_date)[1]', 'Date') as Date..

  In the example the second entry has no title entry , so when I get to the 3rd index the third title is put in the second index

  What I need is that the 2nd index for title is null

  Any info on this would be very much appreciated

   

  Thanks

   

   

  • This topic was modified 3 years, 6 months ago by  jbalbo.
  • This topic was modified 3 years, 6 months ago by  jbalbo.

• Erland Sommarskog

  SSC-Insane

  Points: 24038

  More actions

  July 9, 2021 at 9:12 pm

  #3906272

  Your question is not very clear. You talk about 3rd index, but in your example you only have [1]. And (/Catalog/book/title)[3] does not make much sense, since there is only one book node in the document. Could you clarify?

  I would recommend that you post a full script that populates a variable and then a query against that. Please also use the Code Sample button, to make the post easier to read.

   

  [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]