Issue in reading xml in stored procedure in SQL Server

Question

Please assist me, I am getting null value for all columns in select statement. I have tried so many small changes, but still not getting null values .

DECLARE @XML AS XML,@hDoc AS INT, @SQL NVARCHAR (MAX)
SET @XML = N' <Customer>
       <CustomerID>4</CustomerID>
       <Citycode>BBY</Citycode>
       <TitleCode>1</TitleCode>
       <AccountTypeCode>SV</AccountTypeCode>
       <AccRiskCode>A</AccRiskCode>
       <BankBranchCode>BAU001</BankBranchCode>
   </Customer>'

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT 
    CustomerID, Citycode, TitleCode, AccountTypeCode, AccRiskCode, BankBranchCode
FROM OPENXML(@hDoc, 'Customer')
WITH 
(
CustomerID int  '@CustomerID',
Citycode [varchar](10) '@Citycode',
TitleCode int '@TitleCode',
AccountTypeCode varchar(4) '@AccountTypeCode',
AccRiskCode varchar(4) '@AccRiskCode',
BankBranchCode varchar(10) '@BankBranchCode'
)   

EXEC sp_xml_removedocument @hDoc
GO

You are mapping the columns with attributes but not elements, just remove all the @ prefixing the mapped names (@CustomerID should be just CustomerID), then it works OK. — King King
– King King, Commented Apr 24, 2016 at 7:10

marc_s · Accepted Answer · 2016-04-24 07:01:42Z

1

Try this - use the built-in, native XQuery support instead of the deprecated OPENXML :

SELECT
    ID = XC.value('CustomerID[1]', 'int'),
    CityCode = XC.value('Citycode[1]', 'varchar(20)'),
    TitleCode = XC.value('TitleCode[1]', 'int'),
    AccountTypeCode = XC.value('AccountTypeCode[1]', 'varchar(20)'),
    AccRiskCode = XC.value('AccRiskCode[1]', 'varchar(20)'),
    BankBranchCode = XC.value('BankBranchCode[1]', 'varchar(20)')
FROM
    @XML.nodes('/Customer') AS XT(XC)

This returns this output:

answered Apr 24, 2016 at 7:01

marc_s

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

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

Collectives™ on Stack Overflow

Issue in reading xml in stored procedure in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related