Query XML in SQL Server 2014

Question

This is my table

CREATE TABLE tab_customerxml 
(
    id INT IDENTITY, 
    xmldata XML
)

and I loaded an xml with insert.

I want to run this simple query

SELECT 
    xmldata.query('/customer')
FROM 
    tab_customerxml 
WHERE 
    id = 4;

but I get this error:

Can not run because select the settings of the following SET options are incorrect QUOTED_IDENTIFIER.

I do not know how to solve this problem.

Thank you

Side note: since your id column is of type int, you should use this notation in your WHERE clause: WHERE id = 4 - don't add those unnecessary single quotes - this makes our '4' a string, which has to be implicitly converted back to an Int ..... if it's an int - don't use any single quotes to begin with — marc_s
– marc_s, Commented May 25, 2016 at 20:56

marc_s · Accepted Answer · 2016-05-25 21:05:14Z

1

Try to run this:

drop your table
```
DROP TABLE tab_customerxml;
```

re-create it with the proper settings on:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON

CREATE TABLE tab_customerxml 
(
   id INT IDENTITY, 
   xmldata XML
)

now try your query again

SELECT xmldata.query('/customer')
FROM tab_customerxml 
WHERE id = 4;

Does that help?

answered May 25, 2016 at 21:05

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

2 Comments

Genny Over a year ago

thanks it works. but I did not understand what was the problem

marc_s Over a year ago

@Genny: as the error clearly says: the setting for QUOTED_IDENTIFIER was wrong. Not sure why - but adding these three SET .... ON lines before creating your table works for me

Collectives™ on Stack Overflow

Query XML in SQL Server 2014

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related