SQL query to retrieve value from a XML column

Question

I have a table which contains a XML column and my data looks something like this:

<Entries>   
    <Entry Default="No" ValueId="1" Sequence="1" Deleted="Yes"><Undefined></Entry>    
    <Entry Default="No" ValueId="2" Sequence="1">Non-Purulent</Entry>    
    <Entry Default="No" ValueId="4" Sequence="2" Deleted="Yes">Sanguinous</Entry>    
    <Entry Default="No" ValueId="11" Sequence="2">Odor Present</Entry>    
    <Entry Default="No" ValueId="3" Sequence="3">Purulent</Entry>    
    <Entry Default="No" ValueId="5" Sequence="3" Deleted="Yes">Serosangiunous</Entry>    
    <Entry Default="No" ValueId="6" Sequence="4">Sanguineous</Entry>    
    <Entry Default="No" ValueId="7" Sequence="4" Deleted="Yes">Serosanguineous</Entry>    
    <Entry Default="No" ValueId="8" Sequence="5">Serous</Entry>    
    <Entry Default="No" ValueId="10" Sequence="6">Sero-sanguineous</Entry>    
    <Entry Default="No" ValueId="9" Sequence="7">Other</Entry>    
</Entries>

I want to retrieve the values which do not have the Deleted = "Yes" attribute. I am very new to using XML queries, so I'll appreciate all the help that I can get.

marc_s · Accepted Answer · 2014-02-13 08:25:18Z

1

If you're writing SQL as your tags and you really mean SQL Server by that - then you can use something like this (assuming your table is called YourTable and the column holding the XML is called XmlColumn - please adapt to your actual situation!):

-- define a CTE (Common Table Expression) and extract detail info from XML 
;WITH XmlCte AS
(
    SELECT
        ID,
        XmlText = XC.value('(.)[1]', 'varchar(20)'),
        XmlValueId = XC.value('(@ValueId)[1]', 'int'),
        XmlSequence = XC.value('(@Sequence)[1]', 'int'),
        XmlDeleted = XC.value('(@Deleted)[1]', 'varchar(10)')
    FROM dbo.YourTable
    CROSS APPLY XmlColumn.nodes('/Entries/Entry') AS XT(XC)
)
SELECT
    XmlValueId,
    XmlSequence,
    XmlText
FROM XmlCte
WHERE XmlDeleted IS NULL

answered Feb 13, 2014 at 8:25

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shaswati Das Over a year ago

Thanks a lot marc_s. yes I should have tagged the proper RDBMS here. I will make a note of that. Yes, it is SQL server

StuartLC · Accepted Answer · 2014-02-13 08:27:58Z

0

As per marc_s, please tag your RDBMS

On the offchance you mean SQL Server, here's one way to do it with xquery

SELECT
  Nodes.node.value('@Sequence', 'varchar(50)') AS Sequence,
  Nodes.node.value('(./text())[1]', 'varchar(50)') AS Value
FROM
    MyTable xyz
    cross apply xyz.XmlColumn.nodes('/Entries/Entry[not(@Deleted="Yes")]') as Nodes(node);

SqlFiddle here

Note that you'll need to make your xml well defined i.e. <Undefined>. will either need to be added to a CDATA section, or you'll need to close the tag.

edited Feb 13, 2014 at 8:27

answered Feb 13, 2014 at 8:25

StuartLC

108k18 gold badges225 silver badges295 bronze badges

Collectives™ on Stack Overflow

SQL query to retrieve value from a XML column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related