XML DOC into Table

Question

Post reply

XML DOC into Table

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 1

Last question:

How would you address this?

[IsWithinCityLimits FieldName="IsWithinCityLimits"]

[FieldValue>true[/FieldValue]

[/IsWithinCityLimits]

All I need from this is the FieldValue.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 2

r.value('(n1:BusinessEntityAddresses/n1:BusinessEntityAddress/n1:IsWithinCityLimits/n1:FieldValue)[1]','varchar(30)') as IsWithinCityLimits

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 3

Thanks. I knew I was close but you help push over the edge. I think it's coming together. 😛

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 4

But not quite:

[AdditionalFields]

[DateTimeField FieldName="ActivatedDate"]

[FieldValue IsNull="false" DateTimeValue="1999-03-25T00:00:00-05:00" /]

[/DateTimeField]

[DateTimeField FieldName="ConsultantPromDt"]

[FieldValue IsNull="false" DateTimeValue="1999-03-25T00:00:00-05:00" /]

[/DateTimeField]

[DateTimeField FieldName="ContractReceive"]

[FieldValue IsNull="false" DateTimeValue="2000-06-23T09:56:42-05:00" /]

[/DateTimeField]

[ DateTimeField FieldName="DeactivateDt"]

[FieldValue IsNull="false" DateTimeValue="2002-01-01T00:00:00-06:00" /]

[/DateTimeField]

[/AdditionalFields]

I need to pull the field name and the field value for each field.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 5

with xmlnamespaces('http://www.thatchertech.com/Prowess/Services' as n1)

select r.value('@FieldName','varchar(30)') as FieldName,

r.value('n1:FieldValue[1]/@DateTimeValue','varchar(30)') as FieldValue

from @x.nodes('/ArrayOfExportBusinessEntity/ExportBusinessEntity/n1:Businesses/n1:Business/n1:BusinessEntityBusinessDate/n1:AdditionalFields/n1:DateTimeField') as x(r);

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 6

Not quite what I am after. That give me two fields. FieldName and FieldValue what I need

is let say the fieldname is ActivatedDate and the FieldValue is "1999-03-25T00:00:00-05:00"

I need one field per record:

This is my code so far:

with xmlnamespaces('http://www.thatchertech.com/Prowess/Services' as n1)

select r.value('n1:ExternalReference[1]','int') as ExternalReference,

r.value('n1:ChangeType[1]','int') as ChangeType,

r.value('n1:Salutation[1]/@CodeID','varchar(30)') as Salutation,

--r.value('(n1:Salutation/n1:CodeGuid)[1]/@DecimalValue','varchar(30)') as SalutationCodeGuid,

r.value('n1:FirstName[1]','varchar(30)') as FirstName,

r.value('n1:PreferredName[1]','varchar(30)') as PreferredName,

r.value('n1:LastName[1]','varchar(30)') as LastName,

r.value('n1:AuditNumber[1]/@DecimalValue','varchar(30)') as AuditNumber,

r.value('n1:BusinessEntityType[1]/@CodeID','varchar(30)') as BusinessEntityType,

r.value('n1:Culture[1]/@CultureID','varchar(30)') as Culture,

r.value('n1:Currency[1]/@CurrencyID','varchar(30)') as Currency,

r.value('(n1:BusinessEntityAddresses/n1:BusinessEntityAddress/n1:AddressLine1)[1]','varchar(30)') as AddressLine1,

r.value('(n1:BusinessEntityAddresses/n1:BusinessEntityAddress/n1:City)[1]','varchar(30)') as City,

r.value('(n1:BusinessEntityAddresses/n1:BusinessEntityAddress/n1:PostalCode)[1]','varchar(30)') as PostalCode,

r.value('(n1:BusinessEntityAddresses/n1:BusinessEntityAddress/n1:State/@StateCode)[1]','varchar(30)') as StateCode,

r.value('(n1:BusinessEntityAddresses/n1:BusinessEntityAddress/n1:Country/@CountryCode)[1]','varchar(30)') as CountryCode,

r.value('(n1:BusinessEntityAddresses/n1:BusinessEntityAddress/n1:NlsCounty)[1]','varchar(30)') as County,

r.value('(n1:BusinessEntityAddresses/n1:BusinessEntityAddress/n1:IsWithinCityLimits/n1:FieldValue)[1]','varchar(30)') as IsWithinCityLimits,

r.value('(n1:BusinessEntityCommunication/n1:EmailAddress)[1]','varchar(30)') as EmailAddress,

r.value('(n1:BusinessEntityPhone/n1:HomePhone)[1]','varchar(30)') as HomePhone,

r.value('(n1:Businesses/n1:Business/n1:BusinessIdentifier/n1:BusinessGuid/@DecimalValue)[1]','varchar(30)') as BusinessGuid,

r.value('(n1:Businesses/n1:Business/n1:BusinessIdentifier/n1:BusinessEntityNumber/@DecimalValue)[1]','varchar(30)') as BusinessEntityNumber,

r.value('(n1:Businesses/n1:Business/n1:BusinessIdentifier/n1:GenealogyPlan/@PlanID)[1]','varchar(30)') as GenealogyPlanID,

r.value('(n1:Businesses/n1:Business/n1:BusinessIdentifier/n1:EZeroFilledBeeNumber)[1]','varchar(30)') as EZeroFilledBeeNumber,

r.value('(n1:Businesses/n1:Business/n1:GenealogyCompLevel/@LevelID)[1]','varchar(30)') as GenealogyCompLevelID,

r.value('(n1:Businesses/n1:Business/n1:GenealogyStatus/@StatusID)[1]','varchar(30)') as GenealogyStatus,

r.value('(n1:Businesses/n1:Business/n1:BusinessEntityBusinessDate/n1:DateTimeField/@DateTimeValue)[1]','varchar(30)') as ActivatedDate

from @x.nodes('/ArrayOfExportBusinessEntity/ExportBusinessEntity') as x(r);

I need the value for each node like the one in bold for the following:

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 7

Not quite:

I need a single column for each field name containing its' appropriate fieldvalue:

[AdditionalFields]

[DateTimeField FieldName="ActivatedDate"]

[FieldValue IsNull="false" DateTimeValue="1999-03-25T00:00:00-05:00" /]

[/DateTimeField]

[DateTimeField FieldName="ConsultantPromDt"]

[FieldValue IsNull="false" DateTimeValue="1999-03-25T00:00:00-05:00" /]

[/DateTimeField]

[DateTimeField FieldName="ContractReceive"]

[FieldValue IsNull="false" DateTimeValue="2000-06-23T09:56:42-05:00" /]

[/DateTimeField]

[ DateTimeField FieldName="DeactivateDt"]

[FieldValue IsNull="false" DateTimeValue="2002-01-01T00:00:00-06:00" /]

[/DateTimeField]

[/AdditionalFields]

My code so far: