XML in XML query

Question

I need some help with querying XML data in SQL Server 2012. I have a database that has a table Parameters_XML with columns

id, application, parameter_nr flag, value

The parameter_nr is for eg 1 and the value for that parameter is the following:

<Root>
  <Row>
    <Item id="1344" flags="257">
      <Row>
        <Item id="1179" flags="257">
          <Str>Gall Studio Design SRL</Str>
        </Item>
        <Item id="1421" flags="257">
          <Str>22204869</Str>
        </Item>
        ...........................

I need to retrieve the values for all the applications where the item is 1179 and 1421.

Eg: for the application 1 the value for the item 1179 is Gall Studio Design SRL and so on.

So far I have written the following query:

SELECT 
    CAST(x.Value AS XML).value('(/Root/Row/Item[@id="1344"/Row/Item[@id="1179"])[1]', 'nvarchar(100)')
FROM 
    Parameters_Xml x
WHERE 
    parameter_nr = 1

But I get the following error:

XQuery [value()]: Syntax error near ')', expected ']'.

Please help me with the valid path for the items required.

marc_s · Accepted Answer · 2015-10-05 08:52:59Z

1

You need to close your [ after Item[@id="1344" - and if you want to value of the Str subnode, you need to add Str to your XPath:

SELECT 
    CAST(x.Value AS XML).value('(/Root/Row/Item[@id="1344"]/Row/Item[@id="1179"]/Str)[1]', 'nvarchar(100)')
FROM 
    Parameters_Xml x
WHERE 
    parameter_nr = 1

answered Oct 5, 2015 at 8:52

marc_s

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

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

Collectives™ on Stack Overflow

XML in XML query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related