Newbie sp_xml_preparedocument

• (o_0)

  SSC Journeyman

  Points: 79

  More actions

  July 7, 2010 at 8:06 pm

  #224675

  Hi all,

  I'm new to sp_xml_preparedocument

  I'm running code below and is returning null values.

  declare @idocint,

  @xmlvarchar(200)

  set @xml = '

  <CD>

  <TITLE>Empire Burlesque</TITLE>

  <ARTIST>Bob Dylan</ARTIST>

  <COUNTRY>USA</COUNTRY>

  <COMPANY>Columbia</COMPANY>

  <PRICE>10.90</PRICE>

  <YEAR>1985</YEAR>

  </CD>'

  exec sp_xml_preparedocument @idoc output, @xml

  select*

  fromopenxml(@idoc, '/CD', 2)

  with(titlevarchar(25),

  artistvarchar(25),

  countryvarchar(25),

  companyvarchar(25),

  pricedecimal,

  yrchar(4))

  is there something wrong with my code?

• (o_0)

  SSC Journeyman

  Points: 79

  More actions

  July 7, 2010 at 8:12 pm

  #1189959

  by the way, i'm using ms sql server 2005

• Aparna-1

  SSCrazy

  Points: 2339

  More actions

  July 7, 2010 at 8:32 pm

  #1189965

  Hi,

  Lot of the people in the forums recommend using nodes method instead of OpenXML. Read Books online to know the differences.

  You can parse the XML through nodes method

  [Code]

  declare @xml xml

  set @xml = '

  <CD>

  <TITLE>Empire Burlesque</TITLE>

  <ARTIST>Bob Dylan</ARTIST>

  <COUNTRY>USA</COUNTRY>

  <COMPANY>Columbia</COMPANY>

  <PRICE>10.90</PRICE>

  <YEAR>1985</YEAR>

  </CD>'

  select

  T.X.value('TITLE[1]','varchar(25)' ),

  T.X.value('ARTIST[1]','varchar(25)' ),

  T.X.value('COUNTRY[1]','varchar(25)' ),

  T.X.value('COMPANY[1]','varchar(25)' ),

  T.X.value('PRICE[1]','decimal(5,2)' ),

  T.X.value('YEAR[1]','CHAR(4)' )

  from

  @xml.nodes('CD') T(x)

  [/Code]

• Aparna-1

  SSCrazy

  Points: 2339

  More actions

  July 7, 2010 at 8:42 pm

  #1189968

  But If you want to use OpenXML Method this is the correct query syntax

  exec sp_xml_preparedocument @idoc output, @xml

  select *

  from openxml(@idoc, 'CD', 2)

  with (

  title varchar(25) './TITLE',

  artist varchar(25) './ARTIST',

  country varchar(25) './COUNTRY',

  company varchar(25) './COMPANY',

  price decimal './PRICE',

  yr char(4) './YEAR'

  )

  exec sp_xml_removedocument @idoc --removes the handle

• veeren4urs

  SSCrazy

  Points: 2763

  More actions

  July 8, 2010 at 3:21 am

  #1190084

  Hi,

  r u getting NULL values in the all the feilds?

  Thanks,

  Veeren. :hehe:

  Thanks & Regards,
  Veeren.
  Ignore this if you feel i am Wrong. 😉

• LutzM

  SSC Guru

  Points: 107049

  More actions

  July 8, 2010 at 3:51 pm

  #1190536

  (o_0) (7/7/2010)

  ---

  Hi all,

  I'm new to sp_xml_preparedocument

  I'm running code below and is returning null values.

  declare @idocint,

  @xmlvarchar(200)

  set @xml = '

  <CD>

  <TITLE>Empire Burlesque</TITLE>

  <ARTIST>Bob Dylan</ARTIST>

  <COUNTRY>USA</COUNTRY>

  <COMPANY>Columbia</COMPANY>

  <PRICE>10.90</PRICE>

  <YEAR>1985</YEAR>

  </CD>'

  exec sp_xml_preparedocument @idoc output, @xml

  select*

  fromopenxml(@idoc, '/CD', 2)

  with(titlevarchar(25),

  artistvarchar(25),

  countryvarchar(25),

  companyvarchar(25),

  pricedecimal,

  yrchar(4))

  is there something wrong with my code?

  As far as I can see you'd need to use "TITLE" instead of "title" and so forth. In other words, your query need to be case sensitive.

  Other than that, I second Aparna-1 and prefer XQuery over OpenXML.

  ---

  
  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]

• (o_0)

  SSC Journeyman

  Points: 79

  More actions

  July 10, 2010 at 6:43 am

  #1191130

  thanks for the replies guys.

  they were very helpful. 🙂

  any idea which is more efficient to use?

  nodes method or openxml?

  since both of them have the same output.

  just curious though..

• Ram:)

  SSCrazy

  Points: 2246

  More actions

  July 16, 2010 at 3:38 pm

  #1194403

  Hi Everyone,

  As per my knowledge, XML is case sensitive.

  The following may result null value..

  Correct me if i am wrong

  declare @xml xml

  set @xml = '

  <CD>

  <TITLE>Empire Burlesque</TITLE>

  <ARTIST>Bob Dylan</ARTIST>

  <COUNTRY>USA</COUNTRY>

  <COMPANY>Columbia</COMPANY>

  <PRICE>10.90</PRICE>

  <YEAR>1985</YEAR>

  </CD>'

  exec sp_xml_preparedocument @idoc output, @xml

  select *

  from openxml(@idoc, 'cd', 2)

  with (

  title varchar(25) './TITLE',

  artist varchar(25) './ARTIST',

  country varchar(25) './COUNTRY',

  company varchar(25) './COMPANY',

  price decimal './PRICE',

  yr char(4) './YEAR'

  )

  exec sp_xml_removedocument @idoc --removes the handle

  🙂

• LutzM

  SSC Guru

  Points: 107049

  More actions

  July 17, 2010 at 2:06 am

  #1194483

  Ram:) (7/16/2010)

  ---

  Hi Everyone,

  As per my knowledge, XML is case sensitive.

  The following may result null value..

  Correct me if i am wrong

  ...

  You're right, as already explained a few posts back.

  ---

  
  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]

• LutzM

  SSC Guru

  Points: 107049

  More actions

  July 17, 2010 at 2:45 am

  #1194488

  (o_0) (7/10/2010)

  ---

  thanks for the replies guys.

  they were very helpful. 🙂

  any idea which is more efficient to use?

  nodes method or openxml?

  since both of them have the same output.

  just curious though..

  I did google for "compare openxml xquery" and found a few interesting links.

  For example http://www.sqlservercentral.com/Forums/Topic417131-338-1.aspx

  ---

  
  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]