Processing XML prolog by SQL Server XML functions-CodePudding

I have a large database table with an XML column. The XML contents is a kind of document like as below:

<?int-dov version="1.0" encoding="UTF-8" standalone="no"?>
<ds:datastoreItem ds:itemID="{F8484AF4-73BF-45CA-A524-0D796F244F37}" xmlns:ds="http://schemas.openxmlformats.org/officeDocument/2006/customXml"><ds:schemaRefs><ds:schemaRef ds:uri="http://schemas.openxmlformats.org/officeDocument/2006/bibliography"/></ds:schemaRefs></ds:datastoreItem>

I'm seeking a function or fast way to fetch standalone attribute value in a T-SQL query. When I run the below query:

select  XmlContent.query('@standalone') from XmlDocuments

I get this error message:

Msg 2390, Level 16, State 1, Line 4
XQuery [XmlDocuments.XmlContent.query()]: Top-level attribute nodes are not supported

So, I would be appreciated if anybody gives me a solution to address this problem.

CodePudding user response：

You can use the processing-instruction() function to get that.

SELECT @xml.value('./processing-instruction("int-dov")[1]','nvarchar(max)')

Result
version="1.0" encoding="UTF-8" standalone="no"

If you want to get just the standalone part, the only way I've found is to construct an XML node from it:

SELECT CAST(
  N'<x '  
  @xml.value('./processing-instruction("int-dov")[1]','nvarchar(max)')  
  N' />' AS xml).value('x[1]/@standalone','nvarchar(10)'

Result
no

db<>fiddle

CodePudding user response：

Just to complement @Charlieface answer. All credit goes to him.

SQL

DECLARE @xml XML = 
N'<?int-dov version="1.0" encoding="UTF-8" standalone="no"?>
<ds:datastoreItem ds:itemID="{F8484AF4-73BF-45CA-A524-0D796F244F37}"
                  xmlns:ds="http://schemas.openxmlformats.org/officeDocument/2006/customXml">
    <ds:schemaRefs>
        <ds:schemaRef ds:uri="http://schemas.openxmlformats.org/officeDocument/2006/bibliography"/>
    </ds:schemaRefs>
</ds:datastoreItem>';

SELECT col.value('x[1]/@standalone','nvarchar(10)') AS [standalone]
    , col.value('x[1]/@encoding','nvarchar(10)') AS [encoding]
    , col.value('x[1]/@version','nvarchar(10)') AS [version]
FROM (VALUES(CAST(N'<x '  
  @xml.value('./processing-instruction("int-dov")[1]','nvarchar(max)')  
  N' />' AS xml))
) AS tab(col);

Output

 ------------ ---------- --------- 
| standalone | encoding | version |
 ------------ ---------- --------- 
| no         | UTF-8    |     1.0 |
 ------------ ---------- ---------