Where clause on XML column

• kapil_kk

  SSC-Insane

  Points: 21316

  More actions

  February 26, 2014 at 2:15 am

  #284006

  Hi all,

  Here is the table script and some data below:

  Create table XMLwithOpenXML

  (

  ID int identity,

  XMLData XML,

  LoadDatetime Datetime Default(getdate())

  )

  Dummy data for insertion:

  Insert into XMLwithOpenXML(xmldata)

  values ('<Internationalization>

  <contentRecords locale="en_US">

  <contentRecord>

  <contentId>age</contentId>

  <content>AGE</content>

  </contentRecord>

  <contentRecord>

  <contentId>add</contentId>

  <content>ADD</content>

  </contentRecord>

  </contentRecords>

  <contentRecords locale="it_IT">

  <contentRecord>

  <contentId>ago</contentId>

  <content>ago</content>

  </contentRecord>

  <contentRecord>

  <contentId>alertCannotAddUnitForJob</contentId>

  <content>You cannot add unit for this Job.</content>

  </contentRecord>

  </contentRecords>

  </Internationalization>')

  I want to filter out the data on basis of xml tag contentRecords locale for values en_US, it_IT.

  I tried this query but its not giving me desired output:

  select X.value('contentId[1]','nvarchar(4000)') as ContentId,

  X.value('content[1]', 'nvarchar(4000)') as Content

  --,X.value('(Internationalization/contentRecords [@locale = "en_US"])[1]','varchar(10)')

  FROM XMLwithOpenXML A

  CROSS APPLY A.XMLdata.nodes('/Internationalization/contentRecords/contentRecord ') as N(X)

  WHERE A.XMLdata.value('(Internationalization/contentRecords/@locale = "en_US")[1]','varchar(100)') = 'en_US'

  Please help

  _______________________________________________________________
  To get quick answer follow this link:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Igor Micev

  SSC-Dedicated

  Points: 33110

  More actions

  February 26, 2014 at 3:03 am

  #1692188

  Will this help you?

  declare @country nvarchar(20)

  set @country = N'en_US'

  select X.value('contentId[1]','varchar(100)') as ContentId,

  X.value('content[1]', 'varchar(100)') as Content

  FROM XMLwithOpenXML A

  CROSS APPLY A.xmldata.nodes('/Internationalization/contentRecords/contentRecord') as N(X)

  WHERE A.xmldata.exist('/Internationalization/contentRecords[@locale = sql:variable("@country")]') = 1

  Regards,

  Igor

  Igor Micev,My blog: www.igormicev.com

• serg-52

  SSCrazy Eights

  Points: 9913

  More actions

  February 26, 2014 at 3:59 am

  #1692205

  ...

  CROSS APPLY A.xmldata.nodes('/Internationalization/contentRecords[@locale = sql:variable("@country")]/contentRecord') as N(X)

  if i got it right and only contentRecords with the given @locale needed.

• Hunchback

  SSCrazy

  Points: 2649

  More actions

  February 26, 2014 at 9:42 am

  #1692344

  Here you have two possible solutions in case you are also interested in pulling the locale attribute.

  DECLARE @XMLwithOpenXML TABLE

  (

  ID int identity,

  XMLData XML,

  LoadDatetime Datetime Default(getdate())

  );

  Insert into @XMLwithOpenXML(xmldata)

  values ('<Internationalization>

  <contentRecords locale="en_US">

  <contentRecord>

  <contentId>age</contentId>

  <content>AGE</content>

  </contentRecord>

  <contentRecord>

  <contentId>add</contentId>

  <content>ADD</content>

  </contentRecord>

  </contentRecords>

  <contentRecords locale="it_IT">

  <contentRecord>

  <contentId>ago</contentId>

  <content>ago</content>

  </contentRecord>

  <contentRecord>

  <contentId>alertCannotAddUnitForJob</contentId>

  <content>You cannot add unit for this Job.</content>

  </contentRecord>

  </contentRecords>

  </Internationalization>');

  SELECT

  N.x.value('(contentId/text())[1]','nvarchar(4000)') AS ContentId,

  N.x.value('(content/text())[1]', 'nvarchar(4000)') AS Content

  FROM

  @XMLwithOpenXML AS T

  CROSS APPLY

  T.XMLData.nodes('Internationalization/contentRecords[@locale="en_US" or @locale="it_IT"]/contentRecord') AS N(x);

  SELECT

  N0.x.value('@locale[1]', 'nvarchar(128)') AS locale,

  N1.x.value('(contentId/text())[1]','nvarchar(4000)') AS ContentId,

  N1.x.value('(content/text())[1]', 'nvarchar(4000)') AS Content

  FROM

  @XMLwithOpenXML AS T

  CROSS APPLY

  T.XMLData.nodes('Internationalization/contentRecords[@locale="en_US" or @locale="it_IT"]') AS N0(x)

  CROSS APPLY

  N0.x.nodes('contentRecord') AS N1(x);

  GO