XML with a Table column

• charlie-337681

  SSC Enthusiast

  Points: 199

  More actions

  April 24, 2007 at 12:56 am

  #72806

  Hello,

  I have two columns in my table,

  1. customerid - the primary key

  2. productdata - field containing a string text representing XML data e.g. <clsProdDataSingleBill><PhoneNo> <string>0299840140</string> <string>0431552023</string></PhoneNo><Email /><FoxtelAccountNumber /></clsProdDataSingleBill>

  Iam trying to write an SQL procedure to extract the data into columns, my problem is that I require the customerid as one off the columns but it is not apart off the XML string in productdata.

  I wrote the following SP which allows me to view the phone numbers but now how do I also incorporate the customerid so i can use it, the current SP below give me the output

  phoneno

  029984010 0431552023

   

  I need it too be, if only the id was apart off the XML string this would be easy! can someone please help incorporate the column into the XML 

  customerid                       phoneno

  1234                             029984010 0431552023

   

  Declare

  @index int

  Declare

  @xmldoc varchar(8000)

  Set

  @xmldoc='<Dataset>

  <cls>

  <PhoneNo>

  <string>0299840140</string>

  <string>0431552023</string>

  </PhoneNo>

  <Email />

  <FoxtelAccountNumber />

  </cls>

  </Dataset>'

  EXEC

  sp_xml_preparedocument @index OUTPUT, @XMLdoc

  Select

  *

  From

  OpenXML (@index,'Dataset/cls')

  With

  (phoneno varchar(100) 'PhoneNo')

• Adrian Nichols-360275

  SSCertifiable

  Points: 7694

  More actions

  April 25, 2007 at 2:37 am

  #702310

  You could build the customerID in to the XML document. You can then return this in your OpenXML statement.

  CREATE

  TABLE #xml (customerID INT, productData VARCHAR(2000))

  INSERT

  INTO #xml VALUES (1234, '<clsProdDataSingleBill><PhoneNo> <string>0299840140</string> <string>0431552023</string></PhoneNo><Email /><FoxtelAccountNumber /></clsProdDataSingleBill>')

  DECLARE @index INT

  DECLARE @xmldoc VARCHAR(8000)

  SELECT

  @xmldoc= '<dataset><customerID>' + CAST(customerID AS VARCHAR) + '</customerID>' + productData + '</dataset>'

  FROM #xml

  EXEC

  sp_xml_preparedocument @index OUTPUT, @XMLdoc

  SELECT

  *

  FROM OpenXML (@index,'dataset')

  WITH (customerID VARCHAR(100) 'customerID', phoneno varchar(100) 'clsProdDataSingleBill/PhoneNo')

  DROP

  TABLE #xml

  Run the above and your customerID is returned with the PhoneNo strings.

  ---

  
  Ade

  A Freudian Slip is when you say one thing and mean your mother.
  For detail-enriched answers, ask detail-enriched questions...[/url]