Post reply

IN clause for xml datatype

  • January 21, 2013 at 11:51 pm

    #288281

    I have xml something like below in my sql server table

    <z>

    <a>

    <b>1</b>

    <c>2</c>

    </a>

    <a>

    <b>4</b>

    <c>5</c>

    .

    .

    .

    so many tags similar to above

    .

    .

    </a>

    </z>

    I wanted to remove some particular <a> node and it's child node where <b> has element 1,15,20..etc.

    So to do this i have to do manually delete which is not efficient.So i was thinking of using IN command of sql so that i can specify values of <b> which has to checked inside IN command.

    So how can i do it?

    thank you

  • January 22, 2013 at 3:49 am

    #1579519

    Anyone please reply me

  • January 23, 2013 at 10:13 am

    #1580189

    winmansoft (1/21/2013)

    I have xml something like below in my sql server table

    <z>

    <a>

    <b>1</b>

    <c>2</c>

    </a>

    <a>

    <b>4</b>

    <c>5</c>

    .

    .

    .

    so many tags similar to above

    .

    .

    </a>

    </z>

    I wanted to remove some particular <a> node and it's child node where <b> has element 1,15,20..etc.

    So to do this i have to do manually delete which is not efficient.So i was thinking of using IN command of sql so that i can specify values of <b> which has to checked inside IN command.

    So how can i do it?

    thank you

    I think you misunderstand the IN operator. The IN operator in T-SQL is used in WHERE clauses to test a value against a subquery or list of values and returns TRUE when the test value is equal to one of the values returned by the subquery or in the list. Comparison/logical operators cannot be used with the XML datatype.

    If you want to find and replace nodes in XML within SQL Server, you'll need to put the XML documents or fragments in an XML datatype column and use XQuery: http://msdn.microsoft.com/en-us/library/ms345122(v=sql.90).aspx.

    Jason Wolfkill

  • January 23, 2013 at 2:32 pm

    #1580271

    Try here: http://beyondrelational.com/modules/2/blogs/28/posts/10298/xquery-lab-17-how-to-update-the-value-of-an-element.aspx

    If this is not exaclty what you need, there are lots of other examples of manipulating XML as well on this site.

Viewing 4 posts - 1 through 3 (of 3 total)

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