SQL select query to get records based on xml node value?

Question

I have a column of ntext data type and NOT XML. It stores all xml data. I need to get records based on xml node value. =>input value is CpsiaId = 456 and should return all records which has this value in the xml

I tried select * from tableName where convert(xml,column_name).value('data((/root/ProductInformation/CPSIA/CpsiaDetails/Item/CpsiaId)[1])','int') = 456

but it didn't work....any ideas or other way of getting the records based xml node value.

Sample Xml:

<root>
  <ProductInformation>
    <Name> Truck with Battery Charger</Name>
    <Description>Fr.</Description>
    <CPSIA>
      <CpsiaDetails>
        <Item>
          <CpsiaId>456</CpsiaId>
          <CpsiaMessage>waring</CpsiaMessage>
        </Item>
        <Item>
          <CpsiaId>236</CpsiaId>
          <CpsiaMessage>to health</CpsiaMessage>
        </Item>
      </CpsiaDetails>
    </CPSIA>
  </ProductInformation>
</root>

Lucero · Accepted Answer · 2011-05-10 16:00:44Z

4

convert(xml,column_name).exist('/root/ProductInformation/CPSIA/CpsiaDetails/Item[CpsiaId=456]') = 1

This works, and you can replace the constant (456) with an sql:variable() if you need a dynamic value, like so (assuming a numeric variable @i):

'/root/ProductInformation/CPSIA/CpsiaDetails/Item[CpsiaId=sql:variable("@i")]'

Edit:

Sample code as requested:

DECLARE @t nvarchar(MAX);
SET @t = '<root>
  <ProductInformation>
    <Name> Truck with Battery Charger</Name>
    <Description>Fr.</Description>
    <CPSIA>
      <CpsiaDetails>
        <Item>
          <CpsiaId>456</CpsiaId>
          <CpsiaMessage>waring</CpsiaMessage>
        </Item>
        <Item>
          <CpsiaId>236</CpsiaId>
          <CpsiaMessage>to health</CpsiaMessage>
        </Item>
      </CpsiaDetails>
    </CPSIA>
  </ProductInformation>
</root>'

DECLARE @i int;
SET @i = 456;

SELECT convert(xml,@t).exist('/root/ProductInformation/CPSIA/CpsiaDetails/Item[CpsiaId=sql:variable("@i")]')

edited May 10, 2011 at 16:00

answered May 10, 2011 at 15:51

Lucero

60.4k9 gold badges127 silver badges154 bronze badges

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

11 Comments

DotNetDeveloper Over a year ago

@Lucero can you paste dynamic sample query ? i tried it throws an error The argument 1 of the xml data type method "exist" must be a string literal.

Lucero Over a year ago

@user594014, I added a sample as requested. My guess is that your argument for exist is not a valid string (quoted with single quotes ').

DotNetDeveloper Over a year ago

@Lucero ...it worked as expected and thank you..which one is faster my database has 1 million records CTE or your sample

Conrad Frix Over a year ago

@user594014 I've udpated my answer to use both syntaxes you should just test it and see

Lucero Over a year ago

@user594014, I would guess that mine is faster, the CTE version does create many virtual rows with the CROSS APPLY and it needs more XQuery runs over the XML data and a conversion (value method). But sometimes the optimizer works in misterious ways... if you need better performance for running this query often you may want to look into XML indexing.

|

Conrad Frix · Accepted Answer · 2011-05-10 16:53:11Z

0

It may be easier to use a CTE to convert it to XML first and then do a CROSS Apply. Here's a sample

Declare @test table (id int identity, xmlData nvarchar(max))
Insert Into @test 
(xmldata)
Values 
('<root>
  <ProductInformation>
    <Name> Truck with Battery Charger</Name>
    <Description>Fr.</Description>
    <CPSIA>
      <CpsiaDetails>
        <Item>
          <CpsiaId>456</CpsiaId>
          <CpsiaMessage>waring</CpsiaMessage>
        </Item>
        <Item>
          <CpsiaId>236</CpsiaId>
          <CpsiaMessage>to health</CpsiaMessage>
        </Item>
      </CpsiaDetails>
    </CPSIA>
  </ProductInformation>
</root>'),
('<root>
  <ProductInformation>
    <Name> Truck with Battery Charger</Name>
    <Description>Fr.</Description>
    <CPSIA>
      <CpsiaDetails>
        <Item>
          <CpsiaId>999</CpsiaId>
          <CpsiaMessage>waring</CpsiaMessage>
        </Item>
        <Item>
          <CpsiaId>123</CpsiaId>
          <CpsiaMessage>to health</CpsiaMessage>
        </Item>
      </CpsiaDetails>
    </CPSIA>
  </ProductInformation>
</root>')

;with cte as (select id, convert(xml,t.xmlData) xdata from @test t )
SELECT t.*
     from cte inner join @test t
     on cte.id = t.id
    cross apply cte.xdata.nodes('//root/ProductInformation/CPSIA/CpsiaDetails/Item') x(nd)
WHERE
    x.nd.value('CpsiaId[1]','int') =456

Using Lucero's answer for the Select against a table (rather than an xml variable)

DECLARE @i int;
SET @i = 456;
SELECT * 
FROM 
    @test 
WHERE
    convert(xml,xmlData).exist('/root/ProductInformation/CPSIA/CpsiaDetails/Item[CpsiaId=sql:variable("@i")]') = 1

edited May 10, 2011 at 16:53

answered May 10, 2011 at 15:58

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

6 Comments

DotNetDeveloper Over a year ago

@Conrad Frix ...it worked as expected and thank you..which one is faster my database has 1 million records your sample or below another post by Lucero

Conrad Frix Over a year ago

@user594014. I would guess that Lucero's would be better but its just a guess. I would try both and see

DotNetDeveloper Over a year ago

@ConradFrix Yes, you guess was right lucero's approach was faster and I thank you both for helping me out.

DotNetDeveloper Over a year ago

@ConradFrix thanks i found the answer key and kind of hidden.. by the way how do you guys retain color syntax when you copy from visual studio or sql server in the editor...i was never succesfull..can you tell me?

Conrad Frix Over a year ago

@user594014 You select the code block and press the {} button. You can also use Markdown. If you feel like experimenting you can go to the Markdown Sandbox

|

Collectives™ on Stack Overflow

SQL select query to get records based on xml node value?

2 Answers 2

11 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related