QUery XML columns

• David-250683

  Hall of Fame

  Points: 3452

  More actions

  July 20, 2008 at 3:09 pm

  #188311

  I have a table called Accounts with a single column called Column1 that has the XML data type. A portion of the data looks in part as follows:

  (NOTE: For purposes of posting the XML I have removed all of the ">"

  <Webstore xmlns="urn:WebstoreDeploymentSchema.xsd"

  <DatabaseTemplates

  <ServerRoles

  <Clusters

  <Servers

  <Deployment Name="CoreCatalogDB"

  <DescriptionCoreCatalogDB Deployment for Webstore 5.5</Description

  <UseManifest

  <DeploymentDefaults

  <SqlClientSettings

  <SqlServerSettings

  <Accounts

  <Account Name="test\func_catalogdbread_u"

  </Account

  <Account Name="test\func_uodb_user"

  </Account

  </Accounts

  </DeploymentDefaults

  <SqlFailSafeSets

  <DataPartitions

  </Deployment

  </Webstore

  How can I do a query on the Account Element, Name Attribute?

  Desired Result set is

  test\func_catalogdbread_u

  test\func_uodb_user

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  July 20, 2008 at 7:07 pm

  #845463

  We cannot see the XML. Either post it as an attachment or use the simple-talk prettifier here http://extras.sqlservercentral.com/prettifier/prettifier.aspx

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• SDM

  SSCommitted

  Points: 1581

  More actions

  July 20, 2008 at 11:05 pm

  #845482

  I think the following will work for you (hard to check without real data)

  select

  Account.value('(./@Name) [1]' , 'varchar(100)') as accountName

  ,Account.value('(./../../@Name) [1]' , 'varchar(100)') as deploymentName -- in case you also want the deployment name

  from Accounts

  cross apply Column1.nodes('/Webstore/Deployment/DeploymentDefaults/Account') as T1(Account)

  Cheers

  Stephen