CODEWITHSUNDEEP
sql-serverxml
val f
val f

Reputation: 5

sql server parsing xml from select query

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)

Upvotes: 0

Views: 1682

Answers (2)

Alex Kudryashev
Alex Kudryashev

Reputation: 9460

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

Upvotes: 1

RareTyler
RareTyler

Reputation: 131

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.

Upvotes: 0

Related Questions