Processing XML prolog by SQL Server XML functions

Question

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.

Charlieface · Accepted Answer · 2021-10-28 16:15:48Z

2

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

answered Oct 28, 2021 at 16:15

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

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

3 Comments

Yitzhak Khabinsky Over a year ago

Good answer, +1 from my side!

Charlieface Over a year ago

Do you have a way of parsing the processing-instruction? It seems to only return the whole thing as a string

Yitzhak Khabinsky Over a year ago

I am unaware about such functionality in SQL Server XQuery. Please up-vote my suggestion: feedback.azure.com/d365community/idea/…

Yitzhak Khabinsky · Accepted Answer · 2021-10-28 21:04:35Z

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

Collectives™ on Stack Overflow

Processing XML prolog by SQL Server XML functions

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related