Xquery to parse XML tags into columns

Question

I have below piece of XML as part of XML column in table say MyTable

<policystatusdetail id="XXXXXXXXXXXXXXX">
<CurrentUser>ABCDEFG</CurrentUser>
<LastModifiedDateTime>2016-04-02T17:03:01.761</LastModifiedDateTime>
<PolicyStatus>Quote-New-Pending</PolicyStatus>
</policystatusdetail>

I want to extract only PolicyStatus as column.

I am using below query

Select x.r.value('@PolicyStatus','varchar(500)') as PolicyStatus 
from 
(Select cast(XMLData as XML) XMLData from Mytable) s
cross apply s.XMLData.nodes('session/data/policyAdmin/policyStatusdetail') as x(r)

But it returns zero rows. Can anyone help?

It might be an issue with case. If your xml looks like policystatusdetail, you need to look for the node policystatusdetail, not policyStatusdetail. Also, PolicyStatus[1], not @PolicyStatus. Using what you're currently using, it will look for the attribute "PolicyStatus" in the policystatusdetail node (e.g. to find "id", you'd use @id). — ZLK
– ZLK, Commented Dec 14, 2016 at 4:55
Hi Chaitanya, as you are quite new here (btw: Welcome!) please allow me one hint: It is very kind of you to say Thank you (you placed an answer for this), but it would be even kinder, to tick the acceptance check below the (best) answer's vote counter. This will 1) mark this issue as solved 2) make it easier for followers to find the best solution 3) pay points to the answerer and 4) pay points to you. Once you've crossed the 15 points border yourself, you are - additionally - asked to vote on contributions. This is the SO-way to say thank you. Happy Coding! — Gottfried Lesigang
– Gottfried Lesigang, Commented Dec 14, 2016 at 19:08

Gottfried Lesigang · Accepted Answer · 2016-12-14 08:25:12Z

2

There are several flaws:

XML is strictly case-sensitive. Your /policyStatusdetail cannot find <policystatusdetail>
Your own query shows, that there must be more: .nodes('session/data/policyAdmin/policyStatusdetail')
The CAST you use (Select cast(XMLData as XML)) shows, that this XML is - probably - stored within a non-XML column. What is the actual data type?
We cannot know, if there is only one occurance of this node. As the XML is bigger obviously there might be more of them?

If you really need nothing else than the first occurance of <PolicyStatus> this can be done very simply:

SELECT CAST(XMLData AS XML).value('(//PolicyStatus)[1]','nvarchar(max)') AS PolicyStatus
FROM Mytable

The // at the beginning will trigger a deep search and find the first element with the given name...

answered Dec 14, 2016 at 8:25

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nolan Shang · Accepted Answer · 2016-12-14 05:02:56Z

try this:

 DECLARE @xml XML='<policystatusdetail id="XXXXXXXXXXXXXXX">
                     <CurrentUser>ABCDEFG</CurrentUser>
                     <LastModifiedDateTime>2016-04-02T17:03:01.761</LastModifiedDateTime>
                     <PolicyStatus>Quote-New-Pending</PolicyStatus>
                     </policystatusdetail><policystatusdetail id="XXXXXXXXXXXXXXX">
                     <CurrentUser>ABCDEFG</CurrentUser>
                     <LastModifiedDateTime>2016-04-02T17:03:01.761</LastModifiedDateTime>
                     <PolicyStatus>Quote-New-Pending</PolicyStatus>
                     </policystatusdetail>'

   --Method 1.SELECT  s.b.value('PolicyStatus[1]','varchar(500)') FROM @xml.nodes('policystatusdetail') s(b)
   --Method 2.
   SELECT  s.b.value('.','varchar(500)') FROM @xml.nodes('policystatusdetail/PolicyStatus') s(b)

Collectives™ on Stack Overflow

Xquery to parse XML tags into columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related