Reputation: 17
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
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