CONVERTING SQL NVARCHAR(MAX) TO XML and getting a Value from XML string

Question

I am trying read from the nodes of a column -where an XML string is stored. The column is of type NVARCHAR(MAX).

The following is the script to create table - SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[XML_Dummy](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [XMLValue] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

The following is the script to enter values to it -

INSERT INTO [dbo].[XML_Dummy]
           ([Name]
           ,[XMLValue])
     VALUES
           ('abcd'
           ,'<?xml version="1.0" encoding="UTF-8"?>
            <STAFFv xmlns:xsd="http://www.w3.org/2001/XMLSchema"     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                 <EMPLOYEE_NUMBER>123456</EMPLOYEE_NUMBER>
                <TITLE>Mr</TITLE>
                <INITIALS>J</INITIALS>
                <FORENAME>PEARL</FORENAME>
                <SURNAME>HOFFMAN</SURNAME>
                <GENDER>MALE</GENDER>
                <DATE_OF_BIRTH>1992-01-01</DATE_OF_BIRTH>
                  <DEPARTMENT_DESC>SUPER SUPPORT TEAM</DEPARTMENT_DESC>
                  <JOB_TITLE_DESC>GENERAL DOGSBODY</JOB_TITLE_DESC>
                  <ORIGINAL_DATE_JOINED>2014-05-01</ORIGINAL_DATE_JOINED>
                  <CURRENT_EMPLOYEE financialyear="2014">Y</CURRENT_EMPLOYEE>
                  <INTERNAL_EMAIL xsi:nil="true" />
                  <CHANGE_TYPE>INSERT</CHANGE_TYPE>
                  <CHANGE_DATE>2014-03-27</CHANGE_DATE>
            </STAFF>')
GO

My goal is to get the GENDER from this string. However to start with when I am writing the following query -

     declare @XMLVALUECAST_ xml
    declare @XMLVALUECONVERT_ xml

 SELECT 
         @XMLVALUECAST_ = CAST(XMLValue AS XML),
         @XMLVALUECONVERT_ = CONVERT(XML, XMLValue)

      --CAST(CAST(CAST(XMLValue AS NTEXT) AS XML).query('data(/STAFF/GENDER)') AS VARCHAR(10)) AS Gender 
 FROM  
    [dbo].[XML_Dummy]

    print @XMLVALUECAST_
    PRINT @XMLVALUECONVERT_

I get the error - Msg 9402, Level 16, State 1, Line 3 XML parsing: line 1, character 38, unable to switch the encoding.

I am using SQL Server 2012 (11.0.5058.0)

Dmitrij Kultasev · Accepted Answer · 2015-04-29 15:34:52Z

2

First of all why don't you save the column in XML? Another thing that you have error in the tag. I believe that it is supposed to be STAFF, not STAFFv. And the last thing is that UTF-8 is used instead of the UTF-16 for XML encoding. So the final code is:

CREATE TABLE #XML_Dummy
    (
      [ID] [INT] IDENTITY(1, 1)
                 NOT NULL ,
      [Name] [NVARCHAR](50) NULL ,
      [XMLValue] [NVARCHAR](MAX) NULL
    ); 

INSERT  INTO #XML_Dummy
        ( [Name] ,
          [XMLValue]
        )
VALUES
        ( 'abcd' ,
          '<?xml version="1.0" encoding="UTF-8"?>
            <STAFF xmlns:xsd="http://www.w3.org/2001/XMLSchema"     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                 <EMPLOYEE_NUMBER>123456</EMPLOYEE_NUMBER>
                <TITLE>Mr</TITLE>
                <INITIALS>J</INITIALS>
                <FORENAME>PEARL</FORENAME>
                <SURNAME>HOFFMAN</SURNAME>
                <GENDER>MALE</GENDER>
                <DATE_OF_BIRTH>1992-01-01</DATE_OF_BIRTH>
                  <DEPARTMENT_DESC>SUPER SUPPORT TEAM</DEPARTMENT_DESC>
                  <JOB_TITLE_DESC>GENERAL DOGSBODY</JOB_TITLE_DESC>
                  <ORIGINAL_DATE_JOINED>2014-05-01</ORIGINAL_DATE_JOINED>
                  <CURRENT_EMPLOYEE financialyear="2014">Y</CURRENT_EMPLOYEE>
                  <INTERNAL_EMAIL xsi:nil="true" />
                  <CHANGE_TYPE>INSERT</CHANGE_TYPE>
                  <CHANGE_DATE>2014-03-27</CHANGE_DATE>
            </STAFF>'
        );

SELECT
    b.x.value('/STAFF[1]/GENDER[1]', 'varchar(100)')
FROM
    #XML_Dummy a
    CROSS APPLY (
                  SELECT
                   CAST(CAST ([XMLValue] AS VARCHAR(MAX)) AS XML) x
                ) b;
DROP TABLE #XML_Dummy;

edited Apr 29, 2015 at 15:34

answered Apr 29, 2015 at 14:59

Dmitrij Kultasev

5,8658 gold badges53 silver badges108 bronze badges

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

2 Comments

Sabyasachi Mukherjee Over a year ago

Thanks for the response - 1. Firstly sorry for that tag error it was a typo while doing copy and paste. 2. The DB is from an existing system and I am unable to change the data type now. 3. Same is with the UTF. The existing data is saved with UTF-8. Is there any way for UTF -8 and NVARCHAR (Max)?

Curious Watcher Over a year ago

great example, thank you. while testing, I got an error for the first line, <?xml version="1.0" encoding="UTF-8"?>: XML parsing: line 1, character 38, unable to switch the encoding removing first line solves the issue. SQLSERVER V19

Brian Pressler · Accepted Answer · 2015-04-29 14:48:25Z

0

I think your XML string is invalid. There's a letter "v" at the end of your start tag for STAFF:

<STAFFv xmlns:xsd="http://www.w3.org/2001/XMLSchema"     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

To get the gender value you can select it like this:

SELECT @XMLVALUECAST_.value('/*[1]/GENDER[1]','varchar(100)')

answered Apr 29, 2015 at 14:48

Brian Pressler

6,7332 gold badges21 silver badges42 bronze badges

Collectives™ on Stack Overflow

CONVERTING SQL NVARCHAR(MAX) TO XML and getting a Value from XML string

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related