OPENXML Insert/Output - 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 2005 Forums

Transact-SQL (2005)

OPENXML Insert/Output

Author

Topic

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2008-03-11 : 11:54:40

Hi Guru's,


INSERT INTO Books (BookName) SELECT BookName FROM OPENXML (@hDoc, '/Objects/Book', 2) WITH (BooKName nvarchar(255)) xmlBookss WHERE NOT EXISTS (SELECT ID FROM Books WHERE Books.BookName = xmlBooks.BookName)

Where my XML looks like:


<Objects>
<Book>
      <ForeignID>876</ForeignID>
      <BookName>SQLTeam Rocks</BookName>
</Book>
<Book>
      <ForeignID>981</ForeignID>
      <BookName>My SQL must get better</BookName>
</Book>
</Objects>

When I insert run the query I want to get the @@IDENTITY for each row and insert them with the corresponding ForeignID into a lookup table structured:

[ID Auto], [BooksID (from Books table)], [ForeignID (from XML)]

Any thoughts appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-11 : 11:57:58

Try:-
INSERT INTO Books (BookName)
OUTPUT INSERTED.BooksID,other fields... INTO YourLookupTable
SELECT BookName FROM OPENXML (@hDoc, '/Objects/Book', 2) WITH (BooKName nvarchar(255)) xmlBookss WHERE NOT EXISTS (SELECT ID FROM Books WHERE Books.BookName = xmlBooks.BookName)

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2008-03-11 : 12:24:49

Thanks for the quick reply, visakh16

1 question, I am not inserting ForeignID into Books, so I can't include it in my SELECT statement, can I? How do I get it into my lookup table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-11 : 12:36:10

Try like this:-

INSERT INTO Books (BookName)
OUTPUT INSERTED.BooksID,ForeignID INTO YourLookupTable
FROM (XML Query Here)
SELECT BookName FROM OPENXML (@hDoc, '/Objects/Book', 2) WITH (BooKName nvarchar(255)) xmlBookss WHERE NOT EXISTS (SELECT ID FROM Books WHERE Books.BookName = xmlBooks.BookName)

Also refer this too:-

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/41b9962c-0c71-4227-80a0-08fdc19f5fe4.htm

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2008-03-13 : 06:51:27

Hi visakh16,

Almost got it. You truly are a flowing fount of yak knowledge.

I don't follow what you mean here: FROM (XML Query Here)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-13 : 12:29:49

quote:
Originally posted by John Sourcer

Hi visakh16,

Almost got it. You truly are a flowing fount of yak knowledge.

I don't follow what you mean here: FROM (XML Query Here)

FROM clause followed by the query you posted. Have a look into the link. It has such an example.

gal-chonok
Starting Member

1 Post

Posted - 2008-04-17 : 04:08:07

Hello,
I have the same problem.
I've checked the following query:

INSERT INTO Books (BookName)
OUTPUT INSERTED.BooksID,xmlBooks.ForeignID
INTO YourLookupTable
SELECT xmlBooks.BookName
FROM OPENXML (@hDoc, '/Objects/Book', 2)
WITH (BooKName nvarchar(255),ForeignID int) xmlBooks
WHERE NOT EXISTS (SELECT ID FROM Books WHERE Books.BookName = xmlBooks.BookName)
But there is an error:
The multi-part identifier "xmlBooks.ForeignID" could not be bound.
How to avoid this error?

csrock1025
Starting Member

1 Post

Posted - 2009-01-06 : 16:18:25

I wonder anyone able to solve the problem?

Thanks

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2009-01-27 : 04:48:33

Visakh oh great yak. I have this all working BUT can you output inserted into where not exists?


INSERT INTO Books (BookName)
OUTPUT INSERTED.BooksID,ForeignID INTO YourLookupTable
//WHERE NOT EXISTS(***)
FROM (XML Query Here)

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-01-27 : 05:06:17

hi
you can pass the XML Query as (@hDoc, '/Objects/Book', 2)

Avantha Siriwardana

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2009-01-27 : 06:14:56

Thanks Avantha,

I know this. Can you OUTPUT INSERTED blah INTO table WHERE NOT EXISTS(SELECT id FROM table WHERE name = 'blah'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 09:13:10

quote:
Originally posted by John Sourcer

Thanks Avantha,

I know this. Can you OUTPUT INSERTED blah INTO table WHERE NOT EXISTS(SELECT id FROM table WHERE name = 'blah'

i dont think you can do that. what will you be checking inside EXISTS()?

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2009-01-28 : 02:26:12

Hi Visakh16,

Looks like you can't. I wanted to insert the output into a table if it doesn't exist there already. Reckon my logic was a bit screwed.

Thanks for replying. 2008 'MERGE' helped me in the end.

gnaboot
Starting Member

1 Post

Posted - 2009-06-18 : 10:26:52

quote:
Originally posted by gal-chonok

Hello,
I have the same problem.
I've checked the following query:

INSERT INTO Books (BookName)
OUTPUT INSERTED.BooksID,xmlBooks.ForeignID
INTO YourLookupTable
SELECT xmlBooks.BookName
FROM OPENXML (@hDoc, '/Objects/Book', 2)
WITH (BooKName nvarchar(255),ForeignID int) xmlBooks
WHERE NOT EXISTS (SELECT ID FROM Books WHERE Books.BookName = xmlBooks.BookName)
But there is an error:
The multi-part identifier "xmlBooks.ForeignID" could not be bound.
How to avoid this error?

I am having the same issue on an INSERT. I want to OUTPUT a column from the XML that I am not inserting to be used in a subsequent XREF INSERT. Has anyone discovered a creative way around this limitation?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources