February 26, 2014 at 2:15 am
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/
February 26, 2014 at 3:03 am
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
February 26, 2014 at 3:59 am
...
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.
February 26, 2014 at 9:42 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply