Reading XML

  • Hi all,

    I have a table with column XML type. I am trying to read values from XML datatype but I ot anything in output neither any error:

    Please find script below and dummy data:

    Create table XMLwithOpenXML

    (

    ID int identity,

    XMLData XML,

    LoadDatetime Datetime Default(getdate())

    )

    INsert data in to table:

    Insert into XMLwithOpenXML(xmldata)

    values ('<Internationalization>

    <!-- Automatically generated using i18Gen Tool at 1/25/2014 12:14:53 AM-->

    <contentRecords locale="en_US">

    <contentRecord>

    <contentId>ago</contentId>

    <content>ago</content>

    </contentRecord>

    <contentRecord>

    <contentId>alertCannotAddUnitForJob</contentId>

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

    </contentRecord>

    </contentRecords>

    </Internationalization>')

    Query to extract data but its not returning me anything:

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

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

    FROM XMLwithOpenXML A

    CROSS APPLY A.XMLdata.nodes('/Internationalization/ContentRecords/ContentRecord') as N(X)

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

  • heya, I am able to find the problem...

    Just change the query to this and it will work:

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

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

    FROM XMLwithOpenXML A

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

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

  • Hi all,

    can anyone please tell me how can I get hte value from

    <contentRecords locale="en_US">

    as value en_US

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

  • You can check your other thread for the answer.

  • XML tags are case sensitive so you just need to simply change it to correct formatting when requesting data (i.e. ContentRecords becomes contentRecords etc...).

    The below change should resolve your problem.

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

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

    FROM XMLwithOpenXML A

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply