Read dynamic XML

Question

Post reply

Read dynamic XML

vineetbhargav

Mr or Mrs. 500

Points: 522
More actions
January 13, 2015 at 6:32 am

#314549

I have the xml which is dynamically created and we are not sure how to read it. for eg the below xml and the output that should come.
declare @p xml = '<root>
<DOCTOR.SSN>
<OldValue />
<NewValue>111111111</NewValue>
<DisplayName>Social Security Number</DisplayName>
</DOCTOR.SSN>
<DOCTOR.EMAILADDRESS>
<OldValue>abc.com</OldValue>
<NewValue>Julietest@yahoo.com</NewValue>
<DisplayName>Email Address</DisplayName>
</DOCTOR.EMAILADDRESS>
<DOCTOR.USERID>
<OldValue />
<NewValue>16454</NewValue>
</DOCTOR.USERID>
</root>'
OUTPUT:
OLD VALUE NEW VALUE
Social Security Name: NULL Social Security Name:11111111
EMAIL ADDRESS: abc.com EMAIL ADDRESS:Julietest@yahoo.com
USERID: NULL USERID: 16454
if the xml node doesnt have the displayname tag then it should use the node name like in <Doctor.USERID> we dont have display name, so it should take the USERID as the name and the value in old value and new value tag(see bold output).
Can you please help me with this..Thanks in advance.
Thanks And Regards
Vineet Bhargava
vineetbhargav@gmail.com

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

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

What kind of output do you want?

Your output example doesn't represent recordset...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

You should be able to modify the following as per your requirements:

declare @p xml = '<root>

<DOCTOR.SSN>

<OldValue />

<NewValue>111111111</NewValue>

<DisplayName>Social Security Number</DisplayName>

</DOCTOR.SSN>

<DOCTOR.EMAILADDRESS>

<OldValue>abc.com</OldValue>

<NewValue>Julietest@yahoo.com</NewValue>

<DisplayName>Email Address</DisplayName>

</DOCTOR.EMAILADDRESS>

<DOCTOR.USERID>

<OldValue />

<NewValue>16454</NewValue>

</DOCTOR.USERID>

</root>'

SELECT n.value('local-name(.)','varchar(255)') AS Element

,n.value('./DisplayName[1]','varchar(255)') AS ValueName

,n.value('./OldValue[1]','varchar(255)') AS OldValue

,n.value('./NewValue[1]','varchar(255)') AS NewValue

FROM @p.nodes('/root/*') X(n)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

Try this

SELECT COALESCE(x.r.value('(DisplayName/text())[1]','VARCHAR(100)'),PARSENAME(x.r.value('local-name(.)','VARCHAR(20)'),1)) + ':' + COALESCE(x.r.value('(OldValue/text())[1]','VARCHAR(100)'),'NULL') AS [OLD VALUE],

COALESCE(x.r.value('(DisplayName/text())[1]','VARCHAR(100)'),PARSENAME(x.r.value('local-name(.)','VARCHAR(20)'),1)) + ':' + COALESCE(x.r.value('(NewValue/text())[1]','VARCHAR(100)'),'NULL') AS [NEW VALUE]

FROM @p.nodes('/root/*') AS x(r)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Lowell SSC Guru Points: 323518 More actions · Answer 4

are you having troulbe using XQuery?

here's an example based on your data, with that you can format it into columns or whatever.

/*--Results

SSN Email UserID

------------------------------ ------------------------------ ------------------------------

111111111 Julietest@yahoo.com 16454

*/

declare @p xml = '<root>

<DOCTOR.SSN>

<OldValue />

<NewValue>111111111</NewValue>

<DisplayName>Social Security Number</DisplayName>

</DOCTOR.SSN>

<DOCTOR.EMAILADDRESS>

<OldValue>abc.com</OldValue>

<NewValue>Julietest@yahoo.com</NewValue>

<DisplayName>Email Address</DisplayName>

</DOCTOR.EMAILADDRESS>

<DOCTOR.USERID>

<OldValue />

<NewValue>16454</NewValue>

</DOCTOR.USERID>

</root>'

select @p.value('(/root/DOCTOR.SSN/NewValue)[1]','nvarchar(30)') AS SSN,

@p.value('(/root/DOCTOR.EMAILADDRESS/NewValue)[1]','nvarchar(30)') AS Email,

@p.value('(/root/DOCTOR.USERID/NewValue)[1]','nvarchar(30)') AS UserID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

vineetbhargav Mr or Mrs. 500 Points: 522 More actions · Answer 5

thanks Mark, your solution worked for me ..thanks much.

Thanks And Regards
Vineet Bhargava
vineetbhargav@gmail.com