CODEWITHSUNDEEP
sqlsql-serveropenxml
Karan Gandhi
Karan Gandhi

Reputation: 1494

Unable to open xml in SQL Server

This is my sample xml,

<?xml version="1.0" encoding="utf-8"?>
<ArrayOfClientContactDetail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                            xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <ClientContactDetail>
        <PersonName>adsf asfd</PersonName>
        <EmailAddress>[email protected]</EmailAddress>
        <PhoneNumber>9876543210</PhoneNumber>
    </ClientContactDetail>
</ArrayOfClientContactDetail>

This is my sql code:

DECLARE @idoc IN

EXEC sp_xml_preparedocument @idoc OUTPUT
,@ClientDetail;


SELECT *
FROM OPENXML(@idoc, 'ArrayOfClientContactDetail/ClientContactDetail', 2) WITH (
    PersonName VARCHAR(100) '@PersonName'
    ,EmailAddress VARCHAR(100) '@EmailAddress'
    ,PhoneNumber VARCHAR(15) '@PhoneNumber'
    ,CompanyID INT '@CompanyID'
    );

Upvotes: 1

Views: 164

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You are specifying in the ColPattern that you want an attribute instead of a node value.

Change '@PersonName' to 'PersonName'.

And don't forget to call sp_xml_removedocument when you are done with @idoc.

If you are on SQL Server 2005 or later you could use the XML data type instead.

select C.X.value('(PersonName/text())[1]', 'varchar(100)') as PersonName,
       C.X.value('(EmailAddress/text())[1]', 'varchar(100)') as EmailAddress,
       C.X.value('(PhoneNumber/text())[1]', 'varchar(100)') as PhoneNumber,
       C.X.value('(CompanyID/text())[1]', 'varchar(100)') as CompanyID
from @ClientDetail.nodes('ArrayOfClientContactDetail/ClientContactDetail') as C(X);

Upvotes: 2

Related Questions