CODEWITHSUNDEEP
sqlsql-serverxml
Universe
Universe

Reputation: 17

XML parsing error: Expected token 'eof' found 'NAME'. How to load xml data in SQL?

I tried to use SQL Server Management Studio to insert XML data into my table. And I always get parsing error. I wonder how I can pull the data from xml. Below is my code.

    --INSERT VALUES FROM XML IN TEMPORAL TABLE
    INSERT INTO #BAL
    (   BuyAppsLogID,
        loan_amount_desired
      )

    SELECT 
    @BuyAppsLogID,
    loan_amount_desired

    FROM OPENXML (@IdocPreVal,   '/tss_loan_request',2)
    WITH 
    (  
    loan_amount_desired VARCHAR(200) '/data name=loan_amount_desired/'


    ) ResultTable

Here is my sample xml data.

<?xml version="1.0" encoding="utf-8"?> 
<tss_loan_request>
<data name="username">hadrian</data>
<data name="bank_account_type">checking</data>
<data name="bank_length_months">60</data>
<data name="client_ip_address">172.56.20.152</data>
<data name="client_url_root">www.emoneylenders.com</data>
<data name="customer_id">CL530f6128a6e94</data>
<data name="date_dob_d">22</data>
<data name="date_dob_m">07</data>
<data name="date_dob_y">1981</data>
<data name="income_date1_d">07</data>
<data name="income_date1_m">03</data>
<data name="income_date1_y">2014</data>
<data name="income_date2_d">21</data>
<data name="income_date2_m">03</data>
<data name="income_date2_y">2014</data>
<data name="income_direct_deposit">true</data>
<data name="income_frequency">biweekly</data>
<data name="income_monthly">2100</data>
<data name="income_type">employment</data>
<data name="loan_amount_desired">300</data>
</tss_loan_request>

I think it's probably because my path is wrong but I don't know what kind of path is correct. Please help!

Upvotes: 1

Views: 3839

Answers (1)

Jeroen Mostert
Jeroen Mostert

Reputation: 28809

I'm pretty sure the WITH clause of OPENXML does not allow arbitrary XPath with filters. Instead, map all the data, then filter it in the usual way with WHERE:

SELECT CONVERT(INT, [data]) AS loan_amount_desired
FROM OPENXML (@IdocPreVal, '/tss_loan_request/data')
WITH (
    [name] VARCHAR(200),
    [data] VARCHAR(200) '.'
)
WHERE [name] = 'loan_amount_desired'

However, sp_xml_preparedocument/OPENXML is the old (pre-SQL Server 2005) way of processing XML; it is vastly less convenient than using the XML data type and its methods, as those do allow arbitrary XPath:

SELECT @xml.value('/tss_loan_request[1]/data[@name="loan_amount_desired"][1]', 'int') 
  AS loan_amount_desired

Getting a name/value table (if you needed that) would be done with

SELECT 
    [data].value('@name', 'varchar(200)') AS [name], 
    [data].value('.', 'varchar(200)') AS [data]
FROM @xml.nodes('/tss_loan_request/data') AS d([data])

No need to first prepare the document. You also don't need cursors, if you had multiple tss_loan_request nodes to process.

Upvotes: 2

Related Questions