CODEWITHSUNDEEP
sqlxmlt-sql
MoominTroll
MoominTroll

Reputation: 2552

Select XML nodes alongside table values

We have a table which contains XML describing a group of orders which is sent by a remote function. Each row also contains things like an ID, timestamp etc of when the list of orders arrived in our system.

To illustrate:

id | date                     | XML 
1    2012-10-20 06:51:13.683   <customer name="Bill"><order oId="1">...</order><order oId="2>...</customer>
2    2012-10-20 07:30:32.833   <customer name="Ben"><order oId="23">...</order></customer>

I want to select all the orders out, but I also want each order to be selected alongside it's id and date. Some hypothetical results:

 id   |  date                  |  Customer | OrderId
 1      2012-10-20 06:51:13.683    Bill       1
 1      2012-10-20 06:51:13.683    Bill       2
 2      2012-10-20 06:51:13.683    Ben        23

Looking around, I've found lots of answers like Select XML nodes using TSQL but these only deal with selecting the first instance of a node. I want to select every order node along with the other table information it's associated with.

Thanks.

Upvotes: 0

Views: 946

Answers (4)

Adam Wenger
Adam Wenger

Reputation: 17560

You'll have to update the names for your columns, but this is the approach I used:

SELECT xmlviewId, myDate
   , myXml.value('(customer[1]/@name)', 'varchar(10)') AS CustomerName
   , orders.value('@oId', 'VARCHAR(10)') AS OrderId
   , orders.value('../@name', 'VARCHAR(10)') AS AlternateWayToGEtCustomerName
FROM xmlView
CROSS APPLY myXml.nodes('//order') AS c(orders);

SQL Fiddle Example

Here's a good example for xquery value

I added a second way to obtain the customerName, depending on your preference.

Upvotes: 2

roman
roman

Reputation: 117485

SQL Fiddle Example

I think it's more logical to do it in two applies

select
    tt.id,
    tt.date,
    cust.col.value('@name', 'nvarchar(128)') as Customer,
    ord.col.value('@oId', 'int') as OrderId
from tt as tt
    outer apply tt.[xml].nodes('/customer') as cust(col)
    outer apply cust.col.nodes('order') as ord(col)

but you can also do it in one apply

SQL Fiddle Example

select
    tt.id,
    tt.date,
    ord.col.value('../@name', 'nvarchar(128)') as Customer,
    ord.col.value('@oId', 'int') as OrderId
from tt as tt
    outer apply tt.[xml].nodes('/customer/order') as ord(col)

Upvotes: 2

GarethD
GarethD

Reputation: 69789

You can use @ to specify an attribute, so your query would be something like this:

DECLARE @T TABLE (ID INT, [Date] DATETIME, [XML] XML);
INSERT @T VALUES 
    (1, '20121020 06:51:13.683', '<customer name="Bill"><order oId="1"></order><order oId="2"></order></customer>'),
    (2, '20121020 07:30:32.833', '<customer name="Ben"><order oId="23"></order></customer>');

SELECT  ID,
        [Date],
        [Customer] = [XML].value('(customer/@name)[1]', 'nvarchar(200)'),
        [OrderID] = t.Orders.value('(@oId)[1]', 'int')
FROM    @T
        CROSS APPLY [XML].nodes('/customer/order') t (Orders)

Upvotes: 0

Mike Taylor
Mike Taylor

Reputation: 2524

I think this is what you are looking for

Upvotes: 0

Related Questions