February 25, 2014 at 6:40 am
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/
February 25, 2014 at 6:58 am
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/
February 25, 2014 at 11:14 pm
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/
February 26, 2014 at 9:44 am
You can check your other thread for the answer.
February 26, 2014 at 3:20 pm
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