T-SQL query part of xml

Question

My table @T have a XML column xmlcol on SQL Server 2005. I want to query all the rows where certain node contains a particular value.

declare @T table(xmlcol Xml)
insert into @T values('
<root>
<items>
    <item>
        <x1>
            <id>12345678</id>
        </x1>
        <x2>
            <count>12</count>
        </x2>
    </item>
    <item>
        <x1>
            <id>99999999</id>
        </x1>
        <x2>
            <count>10</count>
        </x2>
    </item>
</items>
</root>')

select
[xmlcol].query('/root/items/item')
from @T
where
[xmlcol].exist('/root/items/item/x1/id[contains(.,"12345678")]') = 1

This query shows all , but I need only show ONE , like this:

<item>
  <x1>
    <id>12345678</id>
  </x1>
  <x2>
    <count>12</count>
  </x2>
</item>

I found similar question, but with values only, and I am not able to rewrite query for show nodes. Is it possible to select part of xml which I need?

thanks

Jens Erat · Accepted Answer · 2013-12-02 23:45:30Z

1

The semantics of the query you're using is something like

SELECT
"all nodes from XML document matching XPath expression"
WHERE
"there is one node in XML document matching XPath expression"

You can completely skip the WHERE clause, but move its content into an XPath predicate. XPath predicates set filters to axis steps, imagine them as some kind of node test. You might want to keep the WHERE clause to avoid empty results (in case some column has no matches at all).

select
[xmlcol].query('/root/items/item[x1/id[contains(.,"12345678")]]')
from @T

Try the query on SQL Fiddle.

answered Dec 2, 2013 at 23:45

Jens Erat

39k16 gold badges86 silver badges99 bronze badges

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

2 Comments

urys Over a year ago

Thanks, it's working, but I forget namespace. If I use namespace with prefix and another namespace without prefix, query returns empty results. Query is here sqlfiddle.com/#!3/e1c06/147/0

Jens Erat Over a year ago

Seems reasonable. I think I already saw some version declaring the namespaces outside of XQuery in the SQL, but I'm not sure of that. As this works it's totally fine (and the XQuery way).

Collectives™ on Stack Overflow

T-SQL query part of xml

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related