Post reply

Query out attributes in XML

  • March 4, 2009 at 10:08 am

    #128237

    I have the following XML in my database:

    <PENDING_MODS>

    <DELETE TABLE="SOLUTION">

    <COLUMN CLAUSE="WHERE" NAME="SOLUTION_CDE" VALUE="1" />

    </DELETE>

    <DELETE TABLE="SOL_USERS">

    <COLUMN CLAUSE="WHERE" NAME="SOLUTION_CDE" VALUE="1" />

    <COLUMN CLAUSE="WHERE" NAME="USERS_CDE" VALUE="52" />

    </DELETE>

    </PENDING_MODS>

    And I want to Query out the NAME's and VALUES of all the COLUMNs. I've gotten this far:

    select XML.query('/PENDING_MODS/DELETE/COLUMN[@CLAUSE="WHERE"][1]') from table where id=33

    I can't find any information online on just getting attriubtes, though, only element values and actual XML. I could overhaul and go element centric, is that neccessary?

    ---
    Dlongnecker

  • March 4, 2009 at 3:45 pm

    #953662

    This example from BOL may solve your problem:

    DECLARE @myDoc xml

    DECLARE @ProdID int

    SET @myDoc = '<Root>

    <ProductDescription ProductID="1" ProductName="Road Bike">

    <Features>

    <Warranty>1 year parts and labor</Warranty>

    <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>

    </Features>

    </ProductDescription>

    </Root>'

    SET @ProdID = @myDoc.value('(/Root/ProductDescription/@ProductID)[1]', 'int' )

    SELECT @ProdID

    Greets

    Flo

    The more I learn, the more I know what I do not know
    Blog: Things about Software Architecture, .NET development and T-SQL

    How to Post Data/Code to get the best Help How to Post Performance Problems

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply