Querying XML data in SQL Server 2012

Question

I have a database that has a table Parameters_XML with columns.

id, application, parameter_nr flag, value

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

<Root>
  <Row>
    <Item id="341" flags="1">
      <Str>2</Str>
    </Item>
    <Item id="342" flags="1">
      <Str>10</Str>
    </Item>
    <Item id="2196" flags="1">
      <Str>7REPJ1</Str>
    </Item>
  </Row>
</Root>

I need to retrieve the values for all the applications where the item is 341, 342 and 2196.

Eg: for the application 1 the value for the item 341 is 2 and so on.

I have written the following query:

SELECT cast (value as XML).value('data(/Root/Row/Item[@id="431"],')
FROM Parameters_Xml x
WHERE parameter_nr = 1

I get the following error:

Msg 174, Level 15, State 1, Line 1
The value function requires 2 argument(s).

Why my query is not valid?

down voters should leave comment which is more helpful for improve Q quality — wiretext
– wiretext, Commented Oct 1, 2015 at 12:03

marc_s · Accepted Answer · 2015-10-01 11:03:22Z

1

Try someting like this:

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

You're telling SQL Server to go find the <Item> node (under <Root> / <Row>) with and id=341 (that what I'm assuming - your value in the question doesn't even exist) and then get the first <Str> node under <Item> and return that value

Also: why do you need CAST(x.Value as XML) - if that column contains only XML - why isn't it defined with datatype XML to begin with? If you have this, you don't need any CAST ...

answered Oct 1, 2015 at 11:03

marc_s

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

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

2 Comments

Code_Noob Over a year ago

You just saved my life! Thank you so so much!

Code_Noob Over a year ago

Pls help me again and tell me the path to the item 1179 from this XML: <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’m writing 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 paramameter_nr = 1 But i get the following error: XQuery [value()]: Syntax error near ')', expected ']'.

wiretext · Accepted Answer · 2015-10-01 12:07:13Z

1

DECLARE @str XML;
SET @str = '<Root>
  <Row>
    <Item id="341" flags="1">
      <Str>2</Str>
    </Item>
    <Item id="342" flags="1">
      <Str>10</Str>
    </Item>
    <Item id="2196" flags="1">
      <Str>7REPJ1</Str>
    </Item>
  </Row>
</Root>'

-- if you want specific values then
SELECT 
 xmlData.Col.value('@id','varchar(max)') Item
,xmlData.Col.value('(Str/text())[1]','varchar(max)') Value
FROM @str.nodes('//Root/Row/Item') xmlData(Col)
where xmlData.Col.value('@id','varchar(max)') = 342

--if you want all values then
SELECT 
 xmlData.Col.value('@id','varchar(max)') Item
,xmlData.Col.value('(Str/text())[1]','varchar(max)') Value
FROM @str.nodes('//Root/Row/Item') xmlData(Col)
--where xmlData.Col.value('@id','varchar(max)') = 342

Edit After CommentIf i query my db: select * from parameters_xml where parameter_nr = 1 i will receive over 10000 rows, each row is like the following: Id app param value 1 1 1 11 I need for all the 10000 apps to retrieve the item id and the value from the XML value - like you did for my eg.

 -- declare temp table
declare @temp table
    (val xml)
    insert into @temp values ('<Root>
      <Row>
        <Item id="341" flags="1">
          <Str>2</Str>
        </Item>
        <Item id="342" flags="1">
          <Str>10</Str>
        </Item>
        <Item id="2196" flags="1">
          <Str>7REPJ1</Str>
        </Item>
      </Row>
    </Root>')
    insert into @temp values ('<Root>
      <Row>
        <Item id="3411" flags="1">
          <Str>21</Str>
        </Item>
        <Item id="3421" flags="1">
          <Str>101</Str>
        </Item>
        <Item id="21961" flags="1">
          <Str>7REPJ11</Str>
        </Item>
      </Row>
    </Root>')
-- QUERY
     SELECT 
         xmlData.Col.value('@id','varchar(max)') Item
        ,xmlData.Col.value('(Str/text())[1]','varchar(max)') Value
        FROM @temp AS T
        outer apply T.val.nodes('/Root/Row/Item') as xmlData(Col)

edited Oct 1, 2015 at 12:07

answered Oct 1, 2015 at 9:22

wiretext

3,35216 silver badges19 bronze badges

5 Comments

Code_Noob Over a year ago

Thank you so much for your response, it it excelent! If you could help me with a general select for all the applications, that would be very kind of you. My DataBase has over 10000 values like this and I need to select all of them for all my applications and most of them have much more values in the XML than the eg.

wiretext Over a year ago

@RalucaIoana above answer giving us all the values of XML

Code_Noob Over a year ago

I understand that, but it give us all the values from that XML, if I select all from the table parameters_XML, i have over 10000 row, each row containig it's own XML value. I need a query that returns me all the values for all the 10000, each row having it's own XML value.

wiretext Over a year ago

@RalucaIoana Sorry i didn't get you what you want to achive

Code_Noob Over a year ago

If i query my db: select * from parameters_xml where parameter_nr = 1 i will receive over 10000 rows, each row is like the following: Id app param value 1 1 1 <Root><Row><Item id="341" flags="1"><Str>1</Str></Item><Item id="342" flags="1"><Str>1</Str></Item></Row></Root> I need for all the 10000 apps to retrieve the item id and the value from the XML value - like you did for my eg.

Pawel Czapski · Accepted Answer · 2015-10-01 08:52:38Z

0

Try this:

select cast (value as XML).value('data(/Root/Row/Item[@id="431"]','nvarchar(max)')

data type is 2nd parameter.

answered Oct 1, 2015 at 8:52

Pawel Czapski

1,8742 gold badges17 silver badges29 bronze badges

1 Comment

Code_Noob Over a year ago

Thank you so much for your answer! i have a new error now:Msg 2217, Level 16, State 1, Line 1 XQuery [value()]: ',' or ')' expected

Collectives™ on Stack Overflow

Querying XML data in SQL Server 2012

3 Answers 3

2 Comments

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related