How to read the value of XMLColumn from SQL Server table

Question

I am new to xquery in SQL Server. This the value in ErrorXml column in ReponseRecord table. This is how the sample data for ErrorXML column.

Row1:

<Data>
  <Error PropertyName="CaseDisplayID">
    <ErrorMessage Type="Database">Claim is not in awaiting reprice response state. Current case state is ** Closed (Cancelled) **</ErrorMessage>
  </Error>
</Data>

Row2:

<Data><Error PropertyName="CaseDisplayID" /></Data>

Row3:

<Data />

I want to read the value of Error here, if that attribute exists.

score 3 · Accepted Answer · edited Jan 18 '21 at 12:30

Use .value to get a specific value from your XML and check if the node /Data/Error exists with .exist.

select T.ErrorXML.value('(/Data/Error/@PropertyName)[1]', 'nvarchar(50)') as PropertyName,
       T.ErrorXML.value('(/Data/Error/ErrorMessage/@Type)[1]', 'nvarchar(50)') as ErrorType,
       T.ErrorXML.value('(/Data/Error/ErrorMessage)[1]', 'nvarchar(max)') as ErrorMessage
from ReponseRecord as T
where T.ErrorXML.exist('/Data/Error') = 1

Result:

PropertyName    ErrorType       ErrorMessage
--------------- --------------- ----------------------------------------------------------------------------------------------------
CaseDisplayID   Database        Claim is not in awaiting reprice response state. Current case state is ** Closed (Cancelled) **
CaseDisplayID   NULL            NULL

SE-Data

j0N45 · Answer 2 · 2012-08-06T15:35:02.997

See if this helps

• To check if a certain XML node exists in the XML document:

SELECT xmlColumn.exist('declare namespace x="1"; (/x:ParentNode/x:ChildNode)') AS test
FROM dbo.Table
WHERE Column1=22

• To query the XML data:

SELECT xmlColumn.query('declare namespace x="1"; (/x:ParentNode/x:ChildNode)') AS test
FROM dbo.Table
WHERE Column1=22

I don't think in your case you need the namespaces, in that case just remove them

SELECT xmlColumn.exist('/ParentNode/ChildNode') AS test
FROM dbo.Table
WHERE Column1=22

Sorry I don't have any example for the delete, but it's the same method, you can check here, instead of the "query" or "exist" just use the delete. You can look here for more information http://msdn.microsoft.com/en-us/library/ms190254.aspx

Cheers

How to read the value of XMLColumn from SQL Server table

2 Answers2