Error reading date from XML using OPENXML

• suja srini

  Valued Member

  Points: 60

  More actions

  January 16, 2006 at 12:34 pm

  #67257

  HI

  I have an XML exported into SQL server .I can read all but the date field using OPENXML. Trying to get the date field returns empty rowset.

  <?xml version="1.0" ?>

   <Commission>

     <CopyData>

             <Res_Begin_Date>7/17/2003</Res_Begin_Date>

              <Res_End_Date>12/31/2050</Res_End_Date>

              <Dep_Begin_Date>7/17/2003</Dep_Begin_Date>

                <Dep_End_Date>12/31/2050</Dep_End_Date>

                <Comm_Rate>10</Comm_Rate>

      </CopyData>

    </Commission>

   

  'The following lines are within my stored procedure

  /* Create the XML Document using the above handle */

  EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @strXML

  SELECT * FROM OPENXML (@iDoc, '/Commission/CopyData/Comm_Rate', 2)

              WITH (Comm_Rate real '.')  - Returns 10

   SELECT * FROM OPENXML(@iDoc,'/Commission/CopyData/Res_Begin_date', 2)

              WITH (Res_Begin_Date datetime '.') - returns empty

   

  /* Remove the document from memory */

  EXEC sp_xml_removedocument @iDoc

   

  Can anyone help me find out what's missing

   

  TIA

  Suja

   

   

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  January 16, 2006 at 4:36 pm

  #615028

  EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @strXML

   

  SELECT * FROM OPENXML (@iDoc, '/Commission/CopyData', 2)

              WITH (Comm_Rate real ) -- - Returns 10

   SELECT * FROM OPENXML(@iDoc,'/Commission/CopyData', 2)

              WITH (Res_Begin_Date datetime  ) --- returns empty

   

  /* Remove the document from memory */

  EXEC sp_xml_removedocument @iDoc

  _____________
  Code for TallyGenerator

• Garth Davis-244656

  Grasshopper

  Points: 16

  More actions

  January 17, 2006 at 8:14 am

  #615168

  '/Commission/CopyData/Res_Begin_date'

  should be

  '/Commission/CopyData/Res_Begin_Date'

  i.e. Upper case D in _Date

• suja srini

  Valued Member

  Points: 60

  More actions

  January 17, 2006 at 8:36 am

  #615181

  Thanks. It was the case sensitive _D