How do I query an XML field in SQL

Question

This is the contents of my XML field in SQL:

- <TestingConfig xmlns="http://tempuri.org/TestingConfig.xsd"&gt;
 `- <Config>`
      `<ConfigId>75</ConfigId>` 
      `<PlaceId>2</PlaceId>` 
      `<Identifier>05</Identifier>` 
      `<Name>TEST1</Name>` 
      `</Config>`
  `- <Config>`
      `<ConfigId>76</ConfigId>` 
      `<PlaceId>2</PlaceId>` 
      `<Identifier>06</Identifier>` 
      `<Name>TEST2</Name>` 
      `</Config>`
 `</TestingConfig>`

I need to query it and return the results in rows like this:

    Config ID       PlaceID        Identifier        Name
    75              2              05                TEST1
    76              2              06                TEST2

I need it to not include the namespace in the results. I'm new to XML querying. Thanks for any help.

Answer 1

If you're using SQL Server 2005 or 2008, something like this should work for you ...

DECLARE @xml XML

SELECT @xml = '<TestingConfig>
 <Config>
      <ConfigId>75</ConfigId>
      <PlaceId>2</PlaceId>
      <Identifier>05</Identifier>
      <Name>TEST1</Name>
      </Config>
  <Config>
      <ConfigId>76</ConfigId>
      <PlaceId>2</PlaceId>
      <Identifier>06</Identifier>
      <Name>TEST2</Name>
      </Config>
 </TestingConfig>'

 SELECT node.ref.value( 'ConfigId[1]', 'int' ) AS [ConfigId],
        node.ref.value( 'PlaceId[1]', 'int' ) AS [PlaceId],
        node.ref.value( 'Identifier[1]', 'varchar(32)' ) AS [Ident],
        node.ref.value( 'Name[1]', 'varchar(32)' ) AS [Name]
        FROM @xml.nodes( '/TestingConfig/Config' ) AS node(ref)

Answer 2

This worked for me. Thanks for everyone's responses.

WITH XMLNAMESPACES ('http://tempuri.org/TestingConfig.xsd' AS CC)

SELECT

CC.Config.value('CC:ConfigId[1]', 'int') AS [ConfigId],

CC.Config.value('CC:PlaceId[1]', 'int') AS [PlaceId],

CC.Config.value('CC:Identifier[1]', 'char(2)') AS [Identifier],

CC.Config.value('CC:Name[1]', 'varchar(8)') AS [Name]

FROM TestingConfig

CROSS APPLY TestingConfig.ConfigField.nodes('//CC:Config') AS CC(Config)