CODEWITHSUNDEEP
sqlsql-servert-sql
Code_Noob
Code_Noob

Reputation: 77

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 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?

Upvotes: 1

Views: 612

Answers (3)

wiretext
wiretext

Reputation: 3352

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)

Upvotes: 1

marc_s
marc_s

Reputation: 755361

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 ...

Upvotes: 1

Pawel Czapski
Pawel Czapski

Reputation: 1864

Try this:

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

data type is 2nd parameter.

Upvotes: 0

Related Questions