XML help with a column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

XML help with a column

Author

Topic

charles101
Starting Member

16 Posts

Posted - 2007-04-24 : 02:59:52

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')

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-04-24 : 14:39:08

Looking at your logic, i assume you will do this for one customer at a time. In that case you can store the customer id in a variable for this particlar product data and include it in the select list atlast.

Something like this -

select @customerid, phoenno
from OpenXML (@index,'Dataset/cls')
With (phoneno varchar(100) 'PhoneNo')

If your requirment is different explain the complete picture.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources