want to get the Email information from XML, But getting error

Question

CREATE TABLE XMLTABLE(id int IDENTITY PRIMARY KEY,XML_DATA XML,DATE DATETIME);
go
INSERT INTO XMLTABLE(XML_DATA,DATE)
SELECT CONVERT(XML,BULKCOLUMN)AS DATA,getdate()
FROM OPENROWSET(BULK 'c:\Demo.xml',SINGLE_BLOB)AS x
go
DECLARE @XML AS XML
DECLARE @OUPT AS INT
DECLARE @SQL NVARCHAR (MAX)
SELECT @XML= XML_DATA FROM XMLTABLE
EXEC sp_xml_preparedocument @OUPT OUTPUT,@XML,'<root xmlns:d="http://abc" xmlns:ns2="http://def" />'
SELECT EMAILR
FROM OPENXML(@OUPT,'d:ns2:FORM/ns2:Form1/ns2:Part/ns2:Part1/ns2:Ba')
WITH
(EMAILR [VARCHAR](100) 'ns2:EmailAddress')

EXEC sp_xml_removedocument @OUPT
go

i.e Demo.xml contains>>

<ns2:FORM xmlns="http://abc" xmlns:ns2="http://def">
 <ns2:Form1>
   <ns2:Part>
 <ns2:Part1>
 <ns2:Ba>
    <ns2:EmailA>Hello@YAHOO.COM</ns2:EmailA> ...

Error:Msg 6603, Level 16, State 2, Line 6 XML parsing error: Expected token 'eof' found ':'.

d:ns2-->:<--FORM/ns2:Form1/ns2:Part/ns2:Part1/ns2:Ba

Please ask an actual question. For now we can see only poorly-formatter code, some error message and other parts difficult to identify/understand. — YakovL, Jul 15 '16 at 09:56
[Possible duplicate](http://stackoverflow.com/questions/18705045/select-xml-from-table-in-sql-server) — SND, Jul 15 '16 at 10:17
@SeekAndDestroy the linked question does not cover namespaces which adds quite an amount of troubles... — Shnugo, Jul 15 '16 at 10:20

score 2 · Answer 1 · answered Jul 15 '16 at 10:17

The approach with sp_xml_... methods and FROM OPENXML is outdated!

You should better use the current XML methods .nodes(), .value(), query() and .modify().

Your XML example is not complete, neither is is valid, had to change it a bit to make it working. You'll probably have to adapt the XPath (at least Part1 is missing).

DECLARE @xml XML=
'<ns2:FORM xmlns="http://abc" xmlns:ns2="http://def">
  <ns2:Form1>
    <ns2:Part>
      <ns2:Ba>
        <ns2:EmailA>Hello@YAHOO.COM</ns2:EmailA>
      </ns2:Ba>
    </ns2:Part>
  </ns2:Form1>
</ns2:FORM> ';

This is the secure way with namespaces and full path

WITH XMLNAMESPACES(DEFAULT 'http://abc'
                  ,'http://def' AS ns2)
SELECT @xml.value('(/ns2:FORM/ns2:Form1/ns2:Part/ns2:Ba/ns2:EmailA)[1]','nvarchar(max)');

And this is the lazy approach

SELECT @xml.value('(//*:EmailA)[1]','nvarchar(max)')

You should - however - prefer the full approach. The more you give, the better and fast you get...

want to get the Email information from XML, But getting error

1 Answers1