SQL XML query help

Hi all,

I have XML data contained in a table with field called FormXML in the following format:

<data formcode="CSP_LEADS" formname="Leads">
  <item name="submitdate">2015-10-29T00:01:03.304Z</item>
  <item name="form-data-guid">2D250E03-D8BB-4693-8774-73D61EB3EBF6</item>
  <item name="employeename">Joe Wells</item>
  <item name="employeephonenumber">0411110461</item>
  <item name="leaddetails">Test 2</item>
  <item name="leadcontactdetails">Trees</item>
  <item name="howleadfound">Through the grape vine</item>
</data>

Open in new window


I need to write an SQL view so I can interrogate the data, have thus far come up with this:
select 
	pref.value('(item/text())[3]', 'varchar(50)') as EmpName,
	pref.value('(item/text())[4]', 'varchar(50)') as EmpPhone,
	pref.value('(item/text())[7]', 'varchar(50)') as LeadFound
from   
	FORMINBOUND CROSS APPLY FormXML.nodes('/data') AS Leads(pref)

Open in new window


The above works but assumes line 3 in the XML data will always be Empname.  However the XML data may change so I need to create a view that will always parse "employeename" from the XML data into the view column 'EmpName'.

Any help appreciated, I am very new to SQL query.
alanwellsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
This query
SELECT
      FormXML.query('/data/item[@name="employeename"]/text()')        AS EmpName
    , FormXML.query('/data/item[@name="employeephonenumber"]/text()') AS EmpPhone
    , FormXML.query('/data/item[@name="howleadfound"]/text()')        AS LeadFound
FROM FORMINBOUND
;      
 

Open in new window

Produced this result:
|   EmpName |   EmpPhone |              LeadFound |
|-----------|------------|------------------------|
| Joe Wells | 0411110461 | Through the grape vine |
        

Open in new window

from this data:
CREATE TABLE FORMINBOUND
    ([FormXML] xml)
;
    
INSERT INTO FORMINBOUND
    ([FormXML])
VALUES
    ('<data formcode="CSP_LEADS" formname="Leads">
  <item name="submitdate">2015-10-29T00:01:03.304Z</item>
  <item name="form-data-guid">2D250E03-D8BB-4693-8774-73D61EB3EBF6</item>
  <item name="employeename">Joe Wells</item>
  <item name="employeephonenumber">0411110461</item>
  <item name="leaddetails">Test 2</item>
  <item name="leadcontactdetails">Trees</item>
  <item name="howleadfound">Through the grape vine</item>
</data>
')
;

Open in new window

see it working here: http://sqlfiddle.com/#!6/06a6f/1

Note I cannot vouch for the efficiency of using .query, if you look at the execution plan it appears to be quite expensive.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
btw:

1. for somebody who is "very new to SQL query" you did extremely well and your use of CROSS APPLY is quite advanced.

2. Using XML within SQL isn't simple in my opinion as the syntax can get quite complex

3. I am very familiar with SQL, but do not use XML within SQL. I only get to use it when attempting to answer questions.
1
alanwellsAuthor Commented:
Thanks Paul,

I did a lot of Google time to get as far as I did.

Thanks for the solution, I will check out the link provided.

Cheers,
Alan
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

alanwellsAuthor Commented:
Hi Paul,

I checked the link, nice site.

When you say expensive I assume you mean quite a lot of processing involved in the query so if I were to have many thousands of records it could take time/resources to process?

Cheers,
Alan
0
PortletPaulfreelancerCommented:
yes
"expensive" = takes time/resources to process

note the extra steps in the execution plans. I don't know how much extra this equates to in practice but I believe it will be noticeable.

Your original:
explicit xpath (original)My suggestion"
using .query (new)
1
alanwellsAuthor Commented:
Hi all & Paul,

I was hoping you could help with another query I need to write.

In the XML I have an array as follows:

<data formcode="CSP1010_01" formname="Uniform Order">
  <item name="submitdate">2015-10-28T07:05:40.246Z</item>
  <item name="form-data-guid">F13051AF-BB4E-403C-8439-CF3250921FD9</item>
  <item name="employeename">Alan Wells</item>
  <item name="embroideredname">Wellsy</item>
  <item name="employeephonenumber">0414920461</item>
  <item name="siteprojectlocation">A002 - Brisbane Admin / Corporate</item>
  <item name="companyentity">Bros</item>
  <item name="order_date">2015-10-28</item>
  <array name="OrderDetails">
    <set>
      <item name="uniformitems">Site PPE: Shirt: Hi-Vis: Mens: Orange/Navy</item>
    </set>
    <set>
      <item name="uniformitems">Chef / Food Prep: Apron: Cotton Drill Full Bib: Mens/Womens: Black</item>
    </set>
    <set>
      <item name="uniformitems">Chef / Food Prep: Shirt: Cotton Drill Closed Front Long Sleeve: Mens/Womens: Khaki</item>
    </set>
  </array>
  <item name="field_d9b92828-6d16-4ee8-9902-8c799c163c19" />
  <item name="manageremail">tuser@ostwaldbros.com.au</item>
  <item name="field_c71c41a1-6ff5-42b5-b4e5-645172924255" />
</data>

Open in new window


I have the following SQL query to extract but this is a bit dodgy and if more than 6 items are in the order these will not be in the query.

SELECT        
FormXML.query('/data/item[@name="form-data-guid"]/text()') AS FormGuid, 
FormXML.query('/data/array[@name="OrderDetails"]/set[1]/item[@name="uniformitems"]/text()') AS Item1,                      
FormXML.query('/data/array[@name="OrderDetails"]/set[2]/item[@name="uniformitems"]/text()') AS Item2, 
FormXML.query('/data/array[@name="OrderDetails"]/set[3]/item[@name="uniformitems"]/text()') AS Item3, 
FormXML.query('/data/array[@name="OrderDetails"]/set[4]/item[@name="uniformitems"]/text()') AS Item4, 
FormXML.query('/data/array[@name="OrderDetails"]/set[5]/item[@name="uniformitems"]/text()') AS Item5, 
FormXML.query('/data/array[@name="OrderDetails"]/set[6]/item[@name="uniformitems"]/text()') AS Item6
FROM 
dbo.FORMINBOUND
WHERE 
FormCode = 'CSP1010_01'

Open in new window


Ideally the query would produce a view like this:
form-data-guid				                        OrderItem
F13051AF-BB4E-403C-8439-CF3250921FD9 	Site PPE: Shirt: Hi-Vis: Mens: Orange/Navy</item>
F13051AF-BB4E-403C-8439-CF3250921FD9	Chef / Food Prep: Apron: Cotton Drill Full Bib: Mens/Womens: Black
F13051AF-BB4E-403C-8439-CF3250921FD9	Chef / Food Prep: Shirt: Cotton Drill Closed Front Long Sleeve: Mens/Womens: Khaki

Open in new window


Any help appreciated.

Cheers,
Alan
0
PortletPaulfreelancerCommented:
Alan i will look when i can, but its better if you creata a new question then come to a previous answered question just leaving an invitation/link  to that new question

That way many experts get as alerted to your new question
0
alanwellsAuthor Commented:
I will create a new question, cheers.
0
PortletPaulfreelancerCommented:
ok Alan. Thanks.
0
PortletPaulfreelancerCommented:
Mmmmmmm looks to be beyond my meagre sql/xml skills
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.