CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlxpath
Chaitanya
Chaitanya

Reputation: 33

Xquery to parse XML tags into columns

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?

Upvotes: 2

Views: 369

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67331

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...

Upvotes: 2

Nolan Shang
Nolan Shang

Reputation: 2328

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)

Upvotes: 0

Related Questions