Where clause on XML column

  • 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/

  • 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

  • ...

    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.

  • 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