Help with Select Statement - from XML Table

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)

Help with Select Statement - from XML Table

Author

Topic

ljp099
Yak Posting Veteran

79 Posts

Posted - 2008-05-06 : 16:37:33

I'm trying to get the <Title> node value returned in the select statement below, but cant quite get it to work. I have the <ID> node being returned in the statement, but not the title. Any help is apprecited.

param ids = '<Collection><Content><ID>1</ID><Title>Document 1</Title></Content>< Content><ID>2</ID><Title>Document 2</Title></Content></Collection>'

CREATE PROCEDURE [GetDownloads](@Ids xml) AS

DECLARE @ResearchDocuments TABLE (ID int)

INSERT INTO @ResearchDocuments (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @Ids.nodes('/Collection/Content/ID') as ParamValues(ID)

SELECT * FROM research_downloads INNER JOIN @ResearchDocuments rd ON research_downloads.doc_id = rd.ID Where research_downloads.post_sf = 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-07 : 00:24:24

[code]declare @ids xml
set @ids = '<Collection><Content><ID>1</ID><Title>Document 1</Title></Content><Content><ID>2</ID><Title>Document 2</Title></Content></Collection>'

CREATE PROCEDURE [GetDownloads](@Ids xml) AS

DECLARE @ResearchDocuments TABLE (ID int,Title varchar(20))

INSERT INTO @ResearchDocuments (ID,Title) SELECT ParamValues.ID.value('ID[1]','varchar(20)'),ParamValues.ID.value('Title[1]','varchar(20)')
FROM @Ids.nodes('/Collection/Content') as ParamValues(ID)

SELECT * FROM @ResearchDocuments
SELECT * FROM research_downloads INNER JOIN @ResearchDocuments rd ON research_downloads.doc_id = rd.ID Where research_downloads.post_sf = 0[/code]

ljp099
Yak Posting Veteran

79 Posts

Posted - 2008-05-07 : 11:39:30

Thanks for your help. This works! However, I did not insert the correct xml code.

The title node of the document is a couple nodes deeper than the id node. Here is the xml code:

<Collection><Content><ID>1</ID><Html><root><DocInfo><Title>Document 1</Title></DocInfo></root></Html></Content></Collection>

Can you tell me how I would retrieve the ID node and the Title nodes?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-07 : 11:42:48

INSERT INTO @ResearchDocuments (ID,Title) SELECT ParamValues.ID.value('ID[1]','varchar(20)'),ParamValues.ID.value('Html/root/DocInfo/Title[1]','varchar(20)')
FROM @Ids.nodes('/Collection/Content') as ParamValues(ID)

ljp099
Yak Posting Veteran

79 Posts

Posted - 2008-05-07 : 15:24:48

When I run this statement, I get this error:

INSERT INTO @ResearchDocuments (ID,Title) SELECT ParamValues.ID.value('ID[1]','varchar(20)'),ParamValues.ID.value('Html/root/DocInfo/Title[1]','varchar(100)')
FROM @Ids.nodes('/Collection/Content') as ParamValues(ID)

Msg 2389, Level 16, State 1, Procedure GetUnPostedToSfTrialUserDownloads, Line 17
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

This is the correct path to the Title node however:

/Collection/Content/Html/root/DocInfo/Title

ljp099
Yak Posting Veteran

79 Posts

Posted - 2008-05-09 : 17:19:12

Okay. I finally got the query to run properly. Thanks for all your help.

Working Query:

ALTER PROCEDURE [dbo].[GetUnPostedToSfTrialUserDownloads](@Ids xml) AS

DECLARE @ResearchDocuments TABLE (ID int, Title varchar(250))

INSERT INTO @ResearchDocuments (ID,Title)

SELECT ParamValues.ID.value('ID[1]','varchar(20)'),ParamValues.ID.value('Html[1]/root[1]/DocInfo[1]/Title[1]/.','varchar(250)')

FROM @Ids.nodes('/Collection/Content') as ParamValues(ID)

SELECT distinct dl.item_id, dl.post_sf, dl.insert_date, dl.doc_id, dl.bersin_uid, rd.ID, rd.Title FROM research_downloads dl

INNER JOIN @ResearchDocuments rd ON REPLACE(dl.doc_id, '1033', '') = rd.ID
INNER JOIN users ON dl.bersin_uid = users.uid
INNER JOIN subscriptions ON subscriptions.uID = users.uid

Where dl.post_sf = 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources