August 29, 2011 at 10:09 am
Need help on below XML read and insert...
DECLARE @x XML
SET @x =
'<i18n>
<language id="en_US" version="1.0" context="US English definitions">
<entity id="MyCases" type="end_user" context="My cases" translationRequired="true">My cases</entity>
<entity id="Registration" type="admin_only" translationRequired="false">Registration</entity>
</language>
</i18n>'
--Checking if the specific node row exists in the XML
If exists(SELECT * FROM @x.nodes('//i18n/language/entity') AS x(item) WHERE x.item.value('@id[1]', 'VARCHAR(50)') = 'MyCaseDetails'
and x.item.value('@type[1]', 'VARCHAR(50)') = 'end_user')
--If exists....fetch the value...
SELECT
x.item.value('@id[1]', 'VARCHAR(50)') AS entityid,
x.item.value('@type[1]', 'VARCHAR(50)') AS eType
FROM @x.nodes('//i18n/language/entity') AS x(item)
WHERE x.item.value('@id[1]', 'VARCHAR(50)') = 'MyCaseDetails'
and x.item.value('@type[1]', 'VARCHAR(50)') = 'end_user'
else
--If not exists....insert the new node row...
--insert xxxxxxxxxxxxxxxx
Need syntax help for
1. Inserting new node row into the existing XML block. The output should read
'<i18n>
<language id="en_US" version="1.0" context="US English definitions">
<entity id="MyCases" type="end_user" context="My cases" translationRequired="true">My cases</entity>
**New row---><entity id="MyCaseDetails" type="end_user" context="MyCaseDetails" translationRequired="true">MyCaseDetails</entity>
<entity id="Registration" type="admin_only" translationRequired="false">Registration</entity>
</language>
</i18n>'
2. How do I read the description text between the <entity></entity>.
August 29, 2011 at 11:00 am
Try this:
DECLARE @x XML
SET @x =
'<i18n>
<language id="en_US" version="1.0" context="US English definitions">
<entity id="MyCases" type="end_user" context="My cases" translationRequired="true">My cases</entity>
<entity id="Registration" type="admin_only" translationRequired="false">Registration</entity>
</language>
</i18n>';
--Checking if the specific node row exists in the XML
If exists(SELECT * FROM @x.nodes('//i18n/language/entity') AS x(item) WHERE x.item.value('@id[1]', 'VARCHAR(50)') = 'MyCaseDetails'
and x.item.value('@type[1]', 'VARCHAR(50)') = 'end_user')
--If exists....fetch the value...
SELECT
x.item.value('@id[1]', 'VARCHAR(50)') AS entityid,
x.item.value('@type[1]', 'VARCHAR(50)') AS eType,
x.item.value('(./text())[1]', 'VARCHAR(50)') AS eValue
FROM @x.nodes('//i18n/language/entity') AS x(item)
WHERE x.item.value('@id[1]', 'VARCHAR(50)') = 'MyCaseDetails'
and x.item.value('@type[1]', 'VARCHAR(50)') = 'end_user'
else
--If not exists....insert the new node row...
begin
SET @x.modify('insert <entity id="MyCaseDetails" type="end_user" context="MyCaseDetails" translationRequired="true">My Case Details</entity> into (//i18n/language)[1]');
SELECT
x.item.value('@id[1]', 'VARCHAR(50)') AS entityid,
x.item.value('@type[1]', 'VARCHAR(50)') AS eType,
x.item.value('(./text())[1]', 'VARCHAR(50)') AS eValue
FROM @x.nodes('//i18n/language/entity') AS x(item)
WHERE x.item.value('@id[1]', 'VARCHAR(50)') = 'MyCaseDetails'
and x.item.value('@type[1]', 'VARCHAR(50)') = 'end_user'
END;
SELECT @x;
The key function for inserting rows or values into XML columns/variables is "modify". The odd bit to implementing it is you don't use "SET x = y", you use "SET x.modify", without an equals sign. Took me a while to figure that out the first time I had to do this kind of thing.
Start from here for documentation on it: http://msdn.microsoft.com/en-us/library/ms187093.aspx
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 31, 2011 at 6:46 am
Thank you. It helped.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply