Best Method to Create Large XML String and Parse

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)

Best Method to Create Large XML String and Parse

Author

Topic

ljp099
Yak Posting Veteran

79 Posts

Posted - 2008-07-29 : 14:48:03

I have a table which stores some xml data. I need to retrieve this xml data into one large xml string/block and parse it. What would be the best technique to do this? Currently, Im trying this:

Declare @ResearchXml xml
Declare @i int

Select @ResearchXml = content_html from content where content.folder_id=30

EXEC sp_xml_preparedocument @i OUTPUT, @ResearchXml

SELECT * FROM OPENXML(@i, '/root/DocInfo', 1) WITH
(
Title VARCHAR(250) 'Title',
PrimarySubjectArea VARCHAR(250) 'PrimarySubjectArea',
Author VARCHAR(250) 'Author'
)

This returns a single record only. However, if I run this select statement, I get over 700 records returned:

Select content_html from content where content.folder_id=30

I'm not sure why @ResearchXml is showing only one record and if this is the best way to parse the xml.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 00:35:58

You need to loop through records with content.folder_id=30
and then use OPENXML inside it to fetch the data from xml field in each record. wrap the current openxml call inside a WHILE loop and try. something like

Declare @ResearchXml xml,@PK int
Declare @i int

SELECT @PK=MIN(PK)
FROM content_html 
WHILE @PK IS NOT NULL
BEGIN
Select @ResearchXml = content_html from content where content.folder_id=30 
AND PK=@PK

EXEC sp_xml_preparedocument @i OUTPUT, @ResearchXml

SELECT * FROM OPENXML(@i, '/root/DocInfo', 1) WITH 
(
Title VARCHAR(250) 'Title',
PrimarySubjectArea VARCHAR(250) 'PrimarySubjectArea',
Author VARCHAR(250) 'Author'
)
...

SELECT @PK=MIN(PK)
FROM content_html 
WHERE PK >@PK
END

here PK is the primary key of your content_html table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources