Evaluate XML data in a Text Column SQL 2000 db with OPENQuery

Posted on 2007-10-03
Last Modified: 2012-08-13
I have a fixed set of nodes within my XML that is stored inside of my table/column.

This is my t-sql:  
DECLARE @idoc int
DECLARE @doc varchar(8000)

set @doc = '<specialInstruction><section id="customerSuppliedShippingAccount"></section><section id="customerCare"></section><section id="finance">11x17 and cutting. 4 cuts @ $3.00 per 50 sheets. If binding is chosen, add additional charges. </section><section id="production.kitting"></section><section id="production.inventory"></section><section id="production.finishing">Cut document at crop marks to final size of 13" x 5".</section><section id="production.prePress">Layout document at crop marks to final size of 13" x 5".</section><section id="production.printJob">The final size of this document will be 13x5. Prepress will provide a replacement file.</section><section id="production.shipping"></section><section id="production.packaging"></section><section id="production.general"></section></specialInstruction>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

FROM       OPENXML (@idoc, 'specialInstruction/section id',1)
            WITH (
customerSuppliedShippingAccount varchar(1000),
customerCare varchar(1000),
finance varchar(1000),
[production.kitting] varchar(1000),
[production.inventory] varchar(1000),
[production.finishing] varchar(1000),
[production.prePress] varchar(1000),
[production.printJob] varchar(1000),
[production.shipping] varchar(1000),
[production.packaging] varchar(1000),
[production.general] varchar(1000)

I get and error "Msg 6603, Level 16, State 1, Line 8
XML parsing error: Expected token 'eof' found 'NAME'.

specialInstruction/section -->id<--"

When I remove the section id I get nulls.

I know that I'm close, what am I missing?


Question by:srussell705
    LVL 6

    Accepted Solution

    SELECT    *
    FROM       OPENXML (@idoc, 'specialInstruction/section',1)
                WITH (SectionID varchar(1000) '@id',SectionText varchar (1000) '.')

    will return a 2 x 11 table.  column for the sectionid and another for its associated text
    LVL 1

    Author Comment


    I appreciate that.


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now