XML data tag value <> store <> in database

Question

question

chetanmedivoxx asked May 14, '18

XML data tag value <> store <> in database

Hello,

here i need help

DECLARE @XMLstr AS VARCHAR(MAX) 
    SELECT @XMLstr = '<Vitals> <VitalDetail ControlID ="VS00042" Value =" < ;12> ;" /> 
    </Vitals>' DECLARE @xml XML SELECT @xml = CAST(REPLACE(@XMLstr, '''', '''') AS XML) 
    SELECT x.v.value('@ControlID[1]', 'NVARCHAR(25)') AS ControlID 
	,x.v.value('@Value[1]', 'NVARCHAR(25)') AS Val 
    FROM @xml.nodes('/Vitals/VitalDetail') x(v)

below xml give result like that

ControlIDValVS00042<12>

but i want below result so i can store value as same in databse

	ControlIDValVS00042
< ;12> ;

@ ThomasRushton @Oleg @JohnM@Lukasz8519

sql-server-2008 xml xquery characters

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2018-05-14T14:45:26Z

ThomasRushton answered May 14, '18

I have been summoned...

...and find that the code as posted throws an error.

Msg 9415, Level 16, State 1, Line 3
XML parsing: line 1, character 53, well formed check: no '<' in attribute value

Is this what the query should have been?

mangledxml.png (12.2 KiB)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2018-05-15T07:32:43Z

chetanmedivoxx answered May 15, '18

Hello @ThomasRushton,

Thanks for your replay, in XML string there is space in Value tag value.

Actually what query return result and what i want result that given in below images

First image you can see query and result.

And actually i want below result please take a look below image.

Please help to get above result that i wants to store in database.

Thanks

querygivingthisresult.png (16.3 KiB)

needthisresult.png (1.9 KiB)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question