June 15, 2016 at 9:56 am
I have this XML example below as an email message and I am having troubles getting all the deeper levels of nodes to pull up.
Basically, I just want to get all of available tags seperated in columns like MSGID,MSGTIME,MsgLang,Sender,SenderFIrstName, SenderLastName,Recipient, RecipentFirstName,RecipentLastName,Number,AccountName,EmailAddress,AttachmentFileName,FileID,FileSize.
Please use the sample codes below.
declare @xml xml
set @xml = N'
<Message>
<MsgID>4FB9</MsgID>
<MsgTime>2019-07-20-00.31.23.000000</MsgTime>
<MsgLang>French</MsgLang>
<Sender DelType="FROM">
<UserInfo>
<FirstName>Jon</FirstName>
<LastName>Doe</LastName>
</UserInfo>
</Sender>
<Recipient DelType = "CC">
<UserInfo>
<FirstName>Peter</FirstName>
<LastName>James</LastName>
<Number>439935</Number>
</UserInfo>
</Recipient>
<Recipient DeliveryType = "CC">
<UserInfo>
<FirstName>Jeremy</FirstName>
<LastName>Long</LastName>
<Number>679935</Number>
<AccountName>LongINC</AccountName>
</UserInfo>
</Recipient>
<Recipient DeliveryType = "CC">
<UserInfo>
<FirstName>Davidson</FirstName>
<LastName>Johnson</LastName>
<Number>65147</Number>
<AccountName>WealthINC</AccountName>
<EmailAddress>DJon@abc.com</EmailAddress>
</UserInfo>
</Recipient>
<Attachment>
<FileName>ady.html</FileName>
<FileID>4FB99584.HTM</FileID>
<FileSize>270</FileSize>
</Attachment>
<Attachment>
<FileName>GD1.pdf</FileName>
<FileID>4FB9C62F.pdf</FileID>
<FileSize>890</FileSize>
</Attachment>
</Message>
'
select
b.value('MSGID[1]', 'NVARCHAR(1000)') as MSGID
from @xml.nodes('/Message') tblmessage (b)
June 15, 2016 at 12:29 pm
XML is case sensitive.
This should help you get moving
SELECT
t.b.value('(MsgID)[1]', 'NVARCHAR(1000)') AS MSGID
, t.b.value('(MsgTime)[1]', 'NVARCHAR(1000)') AS MsgTime
, t.b.value('(MsgLang)[1]', 'NVARCHAR(1000)') AS MsgLang
, t.b.value('(Recipient/UserInfo/FirstName)[1]', 'NVARCHAR(1000)') AS Firstname
, t.b.value('(Recipient/UserInfo/LastName)[1]', 'NVARCHAR(1000)') AS LastName
FROM
@xml.nodes('/Message') t(b);
June 15, 2016 at 12:51 pm
Thank you very much. It works great. I thought I had to put in nodes at thr From clause but rather in the select clause. In this way, We have to figure out which node level these xml elements are for the first and last name. Is there a way we don't have to know which node there are?
June 15, 2016 at 1:07 pm
Do you mean just have Firstname in the query without knowing it's below Recipient/Customer?
No. The path is the path. In the Nodes, you've set the top level for that query.
June 15, 2016 at 1:36 pm
Oh ok. That's fine. I think I understand now how SQL server has to know which level to pull info. Thank you so much. 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply