sql server parsing xml from select query

Question

There is a remote sql server db which I can only access through a web service, passing a sql text to it. The service wraps the passed in sql text into a select statement and returns the result of the query.

For example if I want to execute :

select employeeName, caseNumber from AppTable

I pass into the service 'select employeeName, caseNumber, 12344 vendorId from AppTable', the service will actually execute following:

select * from (select employeeName, caseNumber, 12344 vendorId from AppTable) s where s.vendorId = 12344

The requirement is to get all cases by each employee's boss, so it would be something like

select employeeBoss, count(caseNumber) from AppTable group by employeeBoss

The problem is that the employeeBoss is not stored in the remote database, I will have to pass this information in through the webservice. So lets assume I have an xml like text that I could pass to the service:

<Employee>
   <Name>John Doe</Name>
   <EmployeeBoss>Bill G</EmployeeBoss>
</Employee>
<Employee>
   <Name>Jane Smith</Name>
   <EmployeeBoss>Gordon B</EmployeeBoss>
</Employee>
<Employee>
   <Name>Derek Edward</Name>
   <EmployeeBoss>Gordon B</EmployeeBoss>
</Employee>

Is there any way to parse the XML in a select statement so that I could get the desired result? I am thinking to get something like this

select x.EmployeeBoss, count(distinct caseNumber)
from AppTable a
inner join EmployeeFROMXML x on x.Name = a.EmployeeName
group by x.EmployeeBoss

but I don't have the EmployeeFROMXML table, I just have a string like XML. So how can I transform the string like XML into a sql table to be able to join with the existing AppTable only with a help of SELECT statement. (no variable declarations allowed since I can use only the web service which only supports select statements)

Correct, fixed...thanks for pointing this out.

val f
– val f

2016-09-11 00:44:33 +00:00
Commented Sep 11, 2016 at 0:44 — val f
– val f, Commented Sep 11, 2016 at 0:44

Alex Kudryashev · Accepted Answer · 2016-09-11 01:57:40Z

1

You can do something like this.

with xmlDat as (--read as xml
select cast(
'<Employees>
    <Employee>
       <Name>John Doe</Name>
       <EmployeeBoss>Bill G</EmployeeBoss>
    </Employee>
    <Employee>
       <Name>Jane Smith</Name>
       <EmployeeBoss>Gordon B</EmployeeBoss>
    </Employee>
    <Employee>
       <Name>Derek Edward</Name>
       <EmployeeBoss>Gordon B</EmployeeBoss>
    </Employee>
</Employees>' as xml) e
)
,emp as (--tabulate
select t.v.value('Name[1]','varchar(100)') Name,
t.v.value('EmployeeBoss[1]','varchar(100)') EmployeeBoss
from xmlDat cross apply e.nodes('Employees/Employee') t(v)
)--and use
select emp.EmployeeBoss, count(distinct caseNumber)
from emp
inner join AppTable a on emp.Name = a.EmployeeName
group by emp.EmployeeBoss

Update

The same without CTE

select emp.EmployeeBoss, count(distinct caseNumber) from 
(select t.v.value('Name[1]','varchar(100)') Name,
t.v.value('EmployeeBoss[1]','varchar(100)') EmployeeBoss
from  
(select cast(
'<Employees>
    <Employee>
       <Name>John Doe</Name>
       <EmployeeBoss>Bill G</EmployeeBoss>
    </Employee>
    <Employee>
       <Name>Jane Smith</Name>
       <EmployeeBoss>Gordon B</EmployeeBoss>
    </Employee>
    <Employee>
       <Name>Derek Edward</Name>
       <EmployeeBoss>Gordon B</EmployeeBoss>
    </Employee>
</Employees>' as xml) e
) xdat  cross apply e.nodes('Employees/Employee') t(v)) emp
inner join AppTable a on emp.Name = a.EmployeeName
group by emp.EmployeeBoss

edited Sep 11, 2016 at 1:57

answered Sep 11, 2016 at 1:38

Alex Kudryashev

9,5103 gold badges31 silver badges39 bronze badges

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

2 Comments

val f Over a year ago

this is closer to the desired result, however I cannot use CTE's since the webservice is wrapping the select with another select so I end up with something like select * from (with xmlDat....) which won't work.

val f Over a year ago

thank you very much, this is what I was looking for!

RareTyler · Accepted Answer · 2016-09-11 00:27:09Z

0

You can select xml values using xquery funcitons

SELECT 
[xmlColumn].value('(/Employee//EmployeeBoss/node())[1]', 'varchar(max)') as boss
FROM AppTable a
INNER JOIN xmlTable on [xmlColumn].value('(/Employee//Name/node())[1]', 'varchar(max)') as name = a.EmployeeName

This query isn't tested, but I have used the .value() function before to interact with XML columns and it served me well.

answered Sep 11, 2016 at 0:27

RareTyler

1291 gold badge2 silver badges11 bronze badges

3 Comments

val f Over a year ago

The problem is that I don't have the xmlTable and xmlColumns. I only have a text in XML format.

Dour High Arch Over a year ago

You can convert a NVarChar column to XML with Convert(XML, columnName).

val f Over a year ago

I don't have a nvarchar column nor a table to select from. I just have a string formatted as XML...how to I convert this to a sql table only by using a select statement. ( I know I can use OpenXML but you need to declare a xml handle, call stored proc etc....but I can only use select statement since I run through a web service).

Collectives™ on Stack Overflow

sql server parsing xml from select query

2 Answers 2

Update

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Update

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related