parse xml into sql? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

parse xml into sql?

Author

Topic

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-04-15 : 16:36:55

-- Prepare sample data
DECLARE @h INT,
@XML VARCHAR(8000),
@2k5 XML

SELECT @XML = '
<SalesOrder xmlns:dt="urn:schemas-microsoft-com:datatypes">
<doc_gps_is_MC_trx dt:dt="i2">0</doc_gps_is_MC_trx>
<doc_gps_exchange_time dt:dt="datetime">1/1/1900 12:00:00 AM</doc_gps_exchange_time>
<doc_cy_oadjust_subtotal dt:dt="string" Type="decimal">4</doc_cy_oadjust_subtotal>
<doc_gps_currency_index dt:dt="i2">1007</doc_gps_currency_index>
Avenue</doc_bill_to_street>
<doc_gps_exchange_table_id dt:dt="string" />
<doc_gps_misc_taxscheduleid dt:dt="string" />
<doc_gps_MC_transaction_state dt:dt="i4">0</doc_gps_MC_transaction_state>
<doc_ship_to_state dt:dt="string">MD</doc_ship_to_state>
<doc_bill_to_phone dt:dt="string">410-325-3531</doc_bill_to_phone>
<_Verify_With />
<doc_gps_currency_id dt:dt="string">Z-US$</doc_gps_currency_id>
<doc_bill_to_state dt:dt="string">MD</doc_bill_to_state>
<doc_ship_to_zip dt:dt="string">21206</doc_ship_to_zip>
<doc_gps_rate_calc_method dt:dt="i2">0</doc_gps_rate_calc_method>
<doc_gps_freight_taxable dt:dt="i2">0</doc_gps_freight_taxable>
<_Purchase_Errors />
<doc_currency_compatibility dt:dt="string">cy</doc_currency_compatibility>
<doc_bill_to_country dt:dt="string">United States</doc_bill_to_country>
<doc_cy_total_total dt:dt="string" Type="decimal">7.25000</doc_cy_total_total>
<doc_shipping_total dt:dt="i4">325</doc_shipping_total>
<doc_cc_number dt:dt="string">4828500531471028</doc_cc_number>
<doc_gps_expiration_date dt:dt="datetime">1/1/1900 12:00:00 AM</doc_gps_expiration_date>
< <doc_cy_handling_total dt:dt="string" Type="decimal">0</doc_cy_handling_total>
<doc_bill_to_email <doc_gps_misc_taxable dt:dt="i2">0</doc_gps_misc_taxable>
<doc_ship_to_phone dt:dt="string">410-325-3531</doc_ship_to_phone>
<doc_cy_gps_freight_tax_included dt:dt="string" Type="decimal">0.0</doc_cy_gps_freight_tax_included>
<doc_gps_exchange_date dt:dt="datetime">1/1/1900 12:00:00 AM</doc_gps_exchange_date>
<doc_cy_gps_freight_tax_total dt:dt="string" Type="decimal">0.00000</doc_cy_gps_freight_tax_total>
<doc_cy_gps_misc_tax_total dt:dt="string" Type="decimal">0.00000</doc_cy_gps_misc_tax_total>
</SalesOrder>',
@2k5 = @XML

EXEC sp_xml_preparedocument @h OUTPUT, @XML

SELECT doc_cy_oadjust_subtotal

FROM OPENXML (@h, ' WHAT WILL COME HERE')
WITH (
doc_cy_oadjust_subtotal VARCHAR(100) 'Name'

)

EXEC sp_xml_removedocument @h

can anyone tell me what will come in that maroon coloured? - 'WHAT WILL COME HERE' - instead of '/campaignrequest/requestor'

like i saw one example in this forum like:

<campaignrequest>
<requestor>
<emailaddress>test@test.net</emailaddress>
<name>CS Tester</name>
<company>EEE</company>
<phone>425-283-1480</phone>
</requestor>
<sponsor>
<alias>test@test.net</alias>
<name>CCC Sponsor</name>
</sponsor>
</campaignrequest>
---------------------------------------------

DECLARE @h INT,
@XML VARCHAR(8000),
@2k5 XML

SELECT @XML = '
<campaignrequest>
<requestor>
<emailaddress>test@test.net</emailaddress>
<name>CS Tester</name>
<company>EEE</company>
<phone>425-283-1480</phone>
<phone>555-555-1234</phone>
</requestor>
<sponsor>
<alias>test@test.net</alias>
<name>CCC Sponsor</name>
</sponsor>
</campaignrequest>
',
@2k5 = @XML

EXEC sp_xml_preparedocument @h OUTPUT, @XML

SELECT emailaddress,
name,
company,
phone1,
phone2
FROM OPENXML (@h, '/campaignrequest/requestor')
WITH (
emailaddress VARCHAR(100) 'emailaddress',
name VARCHAR(100) 'name',
company VARCHAR(100) 'company',
phone1 VARCHAR(100) 'phone',
phone2 VARCHAR(100) 'phone'
)

EXEC sp_xml_removedocument @h

thanks if someone help me to figure this out.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-15 : 17:34:45

I think you just replace 'WHAT WILL COME HERE' with '/SalesOrder'

elsasoft.org

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-15 : 23:39:58

Just replace what will come here with Xpath queries i.e xml path which will lead you to your data.

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-04-16 : 08:53:47

thanks Visakh and jezemine but when i m trying both of ur solutions i m getting same error:
XML parsing: line 105, character 25, unexpected end of input
FROM OPENXML (@h, '/Salesorder')
or
FROM OPENXML(@h,'c:\Admin\OrderForm633433908524846107.xml')
getting same xml parsing error:

do u have any idea?

thanks.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-16 : 09:37:39

do you know what XPATH is? this is not it: 'c:\Admin\OrderForm633433908524846107.xml' - that's a file system path.

XPATH is a syntax for identifying elements and attributes in an xml document. You should read up on XPATH before trying to use openxml.

The error you are getting is saying your xml is not well formed. Try pasting your xml into an editor and seeing where it's broken. Probably you have a tag that's not closed.

elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-16 : 09:39:57

also, you should edit your post to take the poor guy's email out of the xml blob. the one that ends with @verizon.net.

it's not right to post it here from a privacy perspective (assuming that's a real address of course)

elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-16 : 09:48:00

here's a working example using a snippet of your xml:


-- Prepare sample data
DECLARE @h INT,
@XML VARCHAR(8000),
@2k5 XML

SELECT @XML = '
<SalesOrder xmlns:dt="urn:schemas-microsoft-com:datatypes">
	<doc_gps_is_MC_trx dt:dt="i2">0</doc_gps_is_MC_trx>
	<doc_gps_exchange_time dt:dt="datetime">1/1/1900 12:00:00 AM</doc_gps_exchange_time>
	<doc_cy_oadjust_subtotal dt:dt="string" Type="decimal">4</doc_cy_oadjust_subtotal>
</SalesOrder>',
@2k5 = @XML

EXEC sp_xml_preparedocument @h OUTPUT, @XML

SELECT subtotal
FROM OPENXML (@h, '/SalesOrder')
WITH (subtotal int 'doc_cy_oadjust_subtotal')
EXEC sp_xml_removedocument @h

elsasoft.org

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-04-16 : 10:21:06

thanks jezemine.. i got u and i edited my post as well remember always..thanks for ur guidance..

well, i have one question: as i don't know XML this is my first experience that i m working on xml into sql...so bare with me..

can you tell me if i have to select all rows than i know i have to do SELECT *
FROM OPENXML ....

but in xml - this 'with'

WITH (....

is there any way or option to select all columns in with clause or i have to write all the columns names particulalry to get it which i want...?

thanks for ur co-operation and guidance..it helps me a lot to beginner like me.

--snippet of xml file
DECLARE @h INT,
@XML VARCHAR(8000),
@2k5 XML

SELECT @XML = '
<SalesOrder xmlns:dt="urn:schemas-microsoft-com:datatypes">
<doc_gps_is_MC_trx dt:dt="i2">0</doc_gps_is_MC_trx>
<doc_gps_exchange_time dt:dt="datetime">1/1/1900 12:00:00 AM</doc_gps_exchange_time>
<doc_cy_oadjust_subtotal dt:dt="string" Type="decimal">4</doc_cy_oadjust_subtotal>
<doc_bill_to_name dt:dt="string">Gracie Ferguson</doc_bill_to_name>
<doc_bill_to_street dt:dt="string">4371 Sheldon Avenue</doc_bill_to_street>
<doc_bill_to_password dt:dt="string">letairis</doc_bill_to_password>
<doc_ship_to_state dt:dt="string">MD</doc_ship_to_state>
<doc_bill_to_phone dt:dt="string">410-325-3531</doc_bill_to_phone>
<doc_payment_method dt:dt="string">credit</doc_payment_method>
- <Items>
- <Items_detail>
<_product_image_file dt:dt="string">56-075N.jpg</_product_image_file>
</Items_detail>
</Items>

</SalesOrder>',
@2k5 = @XML

EXEC sp_xml_preparedocument @h OUTPUT, @XML

SELECT *
FROM OPENXML (@h, '/SalesOrder',4)
WITH (Subtotal int 'doc_cy_oadjust_subtotal',
Mc_trx int 'doc_gps_is_MC_trx',
Exchange_time datetime 'doc_gps_exchange_time',
Name varchar(50) 'doc_bill_to_name',
Addres Varchar(50) 'doc_bill_to_street',
Password varchar(25) 'doc_bill_to_password',
State varchar(25) 'doc_ship_to_state',
Phone varchar(15) 'doc_bill_to_phone',
Email varchar(50) 'doc_ship_to_email',
Payment_Method varchar(30) 'doc_payment_method',
Product_image varchar(max) '_product_image_file'
)
EXEC sp_xml_removedocument @h

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-04-16 : 11:28:45

hey!! can anyone tell me why i m getting only one row in output...

as i have more than 1000s data...so if i want all records...all rows what is the option?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-16 : 11:31:48

Are you using sample code you posted in beginning. It contains only a single SalesOrder elemnt and so you will get only a single record.

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-04-16 : 11:46:04

thanks visakh for replying.

well, yes i m using that sample code..ok so i m getting one record..

and i have been given this xml script only ...can you tell me plz so if i want all records then i need all elements of salesorder - different xml script? or i have to create for all records?

can u give me some idea?

thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-16 : 11:55:06

Yup.What your query does is to find out SalesOrder elements inside the xml and retrieve values from nodes whose names are specified inside with() construct in specified format (varchar,int,..). So if you want more records you need to have more SalesOrder elements in your xml.

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-04-16 : 11:59:41

ok thanks a lot!! visakh for ur help and suggestions.

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-04-16 : 12:04:35

is the new node() the same as openxml now in sql 2005

eg
@xml.node('salesorder') kind of thing

Not to up on the xml to be honsest

ayu
Starting Member

43 Posts

Posted - 2008-04-16 : 14:43:35

well, i have onq questions regarding this given xml code:

DECLARE @h INT,
@XML VARCHAR(8000),
@2k5 XML

SELECT @XML = '
<SalesOrder xmlns:dt="urn:schemas-microsoft-com:datatypes">
<doc_gps_is_MC_trx dt:dt="i2">0</doc_gps_is_MC_trx>
<doc_gps_exchange_time dt:dt="datetime">1/1/1900 12:00:00 AM</doc_gps_exchange_time>
<doc_gps_exchange_time dt:dt="datetime">4/19/2008 13:20:00 AM</doc_gps_exchange_time>
<doc_cy_oadjust_subtotal dt:dt="string" Type="decimal">4</doc_cy_oadjust_subtotal>
<doc_bill_to_name dt:dt="string">Gracie Ferguson</doc_bill_to_name>
<doc_bill_to_street dt:dt="string">4371 Sheldon Avenue</doc_bill_to_street>
<doc_bill_to_password dt:dt="string">letairis</doc_bill_to_password>
<doc_ship_to_state dt:dt="string">MD</doc_ship_to_state>
<doc_ship_to_country dt:dt="string">United States</doc_ship_to_country>
<doc_ship_to_zip dt:dt="string">21206</doc_ship_to_zip>
<doc_bill_to_phone dt:dt="string">410-325-3531</doc_bill_to_phone>
<doc_payment_method dt:dt="string">credit</doc_payment_method>
- <Items>
- <Items_detail>
<_product_image_file dt:dt="string">56-075N.jpg</_product_image_file>
</Items_detail>
</Items>

</SalesOrder>',
@2k5 = @XML

EXEC sp_xml_preparedocument @h OUTPUT, @XML

SELECT *
FROM OPENXML (@h, '/SalesOrder')
WITH (Subtotal int 'doc_cy_oadjust_subtotal',
Mc_trx int 'doc_gps_is_MC_trx',
Exchange_time datetime 'doc_gps_exchange_time',
Name varchar(50) 'doc_bill_to_name',
Addres Varchar(50) 'doc_bill_to_street',
Password varchar(25) 'doc_bill_to_password',
State varchar(25) 'doc_ship_to_state',
Country varchar(30) 'doc_ship_to_country',
Zipcode varchar(10) 'doc_ship_to_zip',
Phone varchar(15) 'doc_bill_to_phone',
Email varchar(50) 'doc_ship_to_email',
Payment_Method varchar(30) 'doc_payment_method',
Product_image varchar(30) '../_product_image_file')
EXEC sp_xml_removedocument @h

in red coloured - time are two - different dates and time then with this code it gives null either the first one 1901 12 am
and also the picture image .jpg is not getting in output - null is getting..if '/salesorder/items/items_detail',2 - u will write then it gives null too..

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-04-16 : 16:15:41

well, ayu,
see red colored lines.
i don't know about times,dates but u 'll get .jpg value.
thanks.

DECLARE @h INT,
@XML VARCHAR(8000),
@2k5 XML

SELECT @XML = '
<SalesOrder xmlns:dt="urn:schemas-microsoft-com:datatypes">
<doc_gps_is_MC_trx dt:dt="i2">0</doc_gps_is_MC_trx>
<doc_gps_exchange_time dt:dt="datetime">1/1/1900 12:00:00 AM</doc_gps_exchange_time>
<doc_gps_exchange_time dt:dt="datetime">4/19/2008 13:20:00 AM</doc_gps_exchange_time>
<doc_cy_oadjust_subtotal dt:dt="string" Type="decimal">4</doc_cy_oadjust_subtotal>
<doc_bill_to_name dt:dt="string">Gracie Ferguson</doc_bill_to_name>
<doc_bill_to_street dt:dt="string">4371 Sheldon Avenue</doc_bill_to_street>
<doc_bill_to_password dt:dt="string">letairis</doc_bill_to_password>
<doc_ship_to_state dt:dt="string">MD</doc_ship_to_state>
<doc_ship_to_country dt:dt="string">United States</doc_ship_to_country>
<doc_ship_to_zip dt:dt="string">21206</doc_ship_to_zip>
<doc_bill_to_phone dt:dt="string">410-325-3531</doc_bill_to_phone>
<doc_payment_method dt:dt="string">credit</doc_payment_method>
<Items>
<Items_detail>
<_product_image_file dt:dt="string">56-075N.jpg</_product_image_file>
</Items_detail>
</Items>

</SalesOrder>',
@2k5 = @XML

EXEC sp_xml_preparedocument @h OUTPUT, @XML

SELECT *
FROM OPENXML (@h, '/SalesOrder')
WITH (Subtotal int 'doc_cy_oadjust_subtotal',
Mc_trx int 'doc_gps_is_MC_trx',
Exchange_time datetime 'doc_gps_exchange_time',
Name varchar(50) 'doc_bill_to_name',
Addres Varchar(50) 'doc_bill_to_street',
Password varchar(25) 'doc_bill_to_password',
State varchar(25) 'doc_ship_to_state',
Country varchar(30) 'doc_ship_to_country',
Zipcode varchar(10) 'doc_ship_to_zip',
Phone varchar(15) 'doc_bill_to_phone',
Email varchar(50) 'doc_ship_to_email',
Payment_Method varchar(30) 'doc_payment_method',
Product_image varchar(30) '_product_image_file')
EXEC sp_xml_removedocument @h

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-04-16 : 16:30:33

hey guys,

i have created temptable:

CREATE TABLE tempxmltab1(XmlCol xml);
GO
INSERT tempxmltab1
SELECT CONVERT(xml, BulkColumn) FROM
OPENROWSET(Bulk 'c:\Admin\test.xml', SINGLE_BLOB) [rowsetresults]

INSERT INTO tempxmltab1
(XmlCol)
SELECT * FROM OPENROWSET(
BULK 'c:\Admin\test.xml',
SINGLE_BLOB) AS x

--select * from tempxmltab1

--SELECT * FROM tempxmltab1 FOR XML path(''), root('Salesorder')

i m getting xml script..but is there any method to get whole table/columns while executing this select statement.

thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources