Inserting records using OPENXML

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

Transact-SQL (2000)

Inserting records using OPENXML

Author

Topic

icdesouza
Starting Member

2 Posts

Posted - 2005-01-19 : 16:52:27

I've got a PhoneNumbers table as follows:

ID
CompanyID
Type
Number

I'm using the following code to read an XML Document and create new records in the PhoneNumbers Table

INSERT INTO PhoneNumbers ( Number,
Type
)
SELECT * FROM OPENXML(@hDoc, '/Company/ContactDetails/PhoneList/Phone',2)
WITH {
Number varchar(15) './Number',
Type varchar(15) './Type'
)

This works fine. My question is how do I insert a value (CompanyID) for each record that is created that is not part of the XML document? ie. I need to create new records with
CompanyID,Type,Number of which Type and Number are part of the XML Document and the CompanyID = 1 or some variable.

Thanks

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-19 : 18:50:08

[code]INSERT INTO PhoneNumbers ( Number,Type)
SELECT
@CompanyID,
Number ,
Type
FROM OPENXML(@hDoc, '/Company/ContactDetails/PhoneList/Phone',2)
WITH {
Number varchar(15) './Number',
Type varchar(15) './Type'
) [/code]

icdesouza
Starting Member

2 Posts

Posted - 2005-01-19 : 20:13:42

Thanks a lot for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources