question

chetanmedivoxx avatar image
chetanmedivoxx asked

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-2008xmlxquerycharacters
10 |1200 characters needed characters left characters exceeded

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

ThomasRushton avatar image
ThomasRushton answered

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 characters needed characters left characters exceeded

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

chetanmedivoxx avatar image
chetanmedivoxx answered

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


10 |1200 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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