Search code examples
xpathxquery-sql

How to get child of xmlelement in sql query without its name?


In sql server xml column I have xml like this:

<Test>  
    <Operations>
        <Operations type="OperationSend">
            <OperationSend>                             
                <ToCompanyId>1</ToCompanyId>
                <Date>2011-05-01T00:00:00</Date>                
            </OperationSend>
        </Operations>
        <Operations type="OperationSell">
            <OperationSell>
                <ToCompanyId>33</ToCompanyId>
                <Amount>12</Amount>
            </OperationSell>
        </Operations>
        <Operations type="OperationEdit">
            <OperationEdit>
                <ToCompanyId>12</ToCompanyId>
                <Date>2011-11-01T00:00:00</Date>    
            </OperationEdit>
        </Operations>
    </Operations>
</Test>

I need to take ToCompanyId from last operation (12). I came to something like this. What should be in ??? when there can be any operation type with ToCompanyId.

select testxml.query('(/Test/Operations/Operations)[last()]/???/ToCompanyId') from dbo.MyXmlTable

Solution

  • Put node() instead of ???

    node() matches all nodes of any kind