Obtain Data from XML column

28 Apr 2009 7:29 PM

Servando Canales

I have a table that contains an XML data column

create table BillingEvents (EventMessage XML)

a sample of the record is:

<BillingRecord xmlns:xsd="http://www.w3.org/2001/XMLSchema"
               xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
               xmlns="http://BillingRecord"
               PrincipalName="AccountName"
               PrincipalType="Account"
               Id="12345678-FFFF-AAAA-BBBB-1234567890AB"
               PurchaseDateTime="2009-04-13 03:40:15Z"
               Price="0.0"
               Status="UnRead"
               resourcename="Moviexxxxxxxxxxx"
               StartDate="0001-01-01 00:00:00Z"
               EndDate="0001-01-01 00:00:00Z" />

How can I get the values of PrincipalExternalId, and Price for the ones that
belong to Status = 'Read' ?

I'm trying something like this but it returns NULL for all rows.

SELECT top 10
eventmessage.value('(/BillingRecord/@PrincipalExternalId)[1]',
'nvarchar(50)') AS PrincipalExternalId
FROM BillingEvents

Any help would be appreciated.
Thank you.
Servando Canales

28 Apr 2009 7:44 PM

Servando Canales

Correction on the column name (PrincipalName):

I have a table that contains an XML data column

create table BillingEvents (EventMessage XML)

a sample of the record is:

<BillingRecord xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xmlns="http://BillingRecord"
                PrincipalName="AccountName"
                PrincipalType="Account"
                Id="12345678-FFFF-AAAA-BBBB-1234567890AB"
                PurchaseDateTime="2009-04-13 03:40:15Z"
                Price="0.0"
                Status="UnRead"
                resourcename="Moviexxxxxxxxxxx"
                StartDate="0001-01-01 00:00:00Z"
                EndDate="0001-01-01 00:00:00Z" /

How can I get the values of PrincipalName, and Price for the ones that
belong to Status = 'Read' ?

I'm trying something like this but it returns NULL for all rows.

SELECT top 10
eventmessage.value('(/BillingRecord/@PrincipalName)[1]',
'nvarchar(50)') AS PrincipalName
FROM BillingEvents

Any help would be appreciated.
Thank you.
Servando Canales

29 Apr 2009 5:41 AM

daw

with xmlnamespaces(default 'http://BillingRecord')
SELECT top 10
eventmessage.value('(/BillingRecord/@PrincipalName)[1]',
'nvarchar(50)') AS PrincipalName
FROM BillingEvents

29 Apr 2009 5:42 AM

daw

with xmlnamespaces(default 'http://BillingRecord')
SELECT top 10
eventmessage.value('(/BillingRecord/@PrincipalName)[1]',
'nvarchar(50)') AS PrincipalName
FROM BillingEvents

29 Apr 2009 2:30 PM

Servando Canales

Thanks.
This works.

Show quoteHide quote

"daw" wrote:

> with xmlnamespaces(default 'http://BillingRecord')
> SELECT top 10
> eventmessage.value('(/BillingRecord/@PrincipalName)[1]',
> 'nvarchar(50)') AS PrincipalName
> FROM BillingEvents
>
>

29 Apr 2009 9:26 AM

Bob

;WITH XMLNAMESPACES ( DEFAULT 'http://BillingRecord')
SELECT
    em.x.value('@PrincipalName[1]', 'nvarchar(50)') AS PrincipalName ,
    em.x.value('@Price[1]', 'nvarchar(50)') AS Price
FROM BillingEvents
    CROSS APPLY eventmessage.nodes('BillingRecord[@Status="Read"]') em(x)

wBob
Hope that helps
Show quoteHide quote

"Servando Canales" wrote:

> Correction on the column name (PrincipalName):
>
>
> I have a table that contains an XML data column
>
> create table BillingEvents (EventMessage XML)
>
> a sample of the record is:
>
> <BillingRecord xmlns:xsd="http://www.w3.org/2001/XMLSchema"
>                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
>                 xmlns="http://BillingRecord"
>                 PrincipalName="AccountName"
>                 PrincipalType="Account"
>                 Id="12345678-FFFF-AAAA-BBBB-1234567890AB"
>                 PurchaseDateTime="2009-04-13 03:40:15Z"
>                 Price="0.0"
>                 Status="UnRead"
>                 resourcename="Moviexxxxxxxxxxx"
>                 StartDate="0001-01-01 00:00:00Z"
>                 EndDate="0001-01-01 00:00:00Z" /
>
>
> How can I get the values of PrincipalName, and Price for the ones that
> belong to Status = 'Read' ?
>
> I'm trying something like this but it returns NULL for all rows.
>
> SELECT top 10
> eventmessage.value('(/BillingRecord/@PrincipalName)[1]',
> 'nvarchar(50)') AS PrincipalName
> FROM BillingEvents
>
>
> Any help would be appreciated.
> Thank you.
> Servando Canales
>
>