Hi,

I have a column named "XMLColumnwhich" in a Table named "demandRequest"contains an XML as follows

<RequestEntity xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="explorer/contact/2011/03/">

  <Checked>false</Checked>
  <Items>
    <ItemEntity>
      <Checked>true</Checked>
      <FromDate>2013-10-01T00:00:00</FromDate>
      <Id1>604395</Id1>
      <Id2>11278003</Id2>
      <Option>Random1</Option>
      <ToDate>2013-11-01T00:00:00</ToDate>
    </ItemEntity>
    <ItemEntity>
      <Checked>true</Checked>
      <FromDate>2013-07-01T00:00:00</FromDate>
      <Id1>604394</Id1>
      <Id2>11277949</Id2>
      <Option>Random1</Option>
      <ToDate>2013-08-01T00:00:00</ToDate>
    </ItemEntity>
  </Items>
  <EmailNotification>1</EmailNotification>
  <EndDate>2013-11-01T00:00:00</EndDate>
  <Level>crossing</Level>
  <LineNumber i:nil="true" />
  <id1>0</id1>
  <id2 i:nil="true" />
  <Version>0</Version>
</RequestEntity>

I would like to get the value of all id1, i tried following but getting NULL as response

SELECT CAST(XMLColumn as xml).value('(/RequestEntity/Items/ItemEntity/Id2/text())[1]', 'varchar(50)') AS P
FROM [demandRequest];

Can you kindly help me here to find out what is the correct way.

Thanks

<RequestEntity xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="explorer/contact/2011/03/">

I would like to get the value of all id1, i tried following but getting NULL as response

SELECT CAST(XMLColumn as xml).value('(/RequestEntity/Items/ItemEntity/Id2/text())[1]', 'varchar(50)') AS P
FROM [demandRequest];

Can you kindly help me here to find out what is the correct way.

Since your xml has a default namespace (xmlns attribute), you'll need to declare it in your query and specify the namespace prefix.  Your text mentions Id1 but your query is for Id2.  Assuming Id2 and you might have more than one per column, you might try something like the example below to extract the Id2 values from each row:

SELECT ItemEntity.Id2.value('.', 'varchar(50)') AS P
FROM (SELECT CAST(XMLColumn AS xml) AS XMLColumn FROM demandRequest) AS XMLColumns
CROSS APPLY XMLColumns.XMLColumn.nodes(
	'declare namespace ex="explorer/contact/2011/03/";
	/ex:RequestEntity/ex:Items/ex:ItemEntity/ex:Id2') AS ItemEntity(Id2);		

Try something like below. Also refer the XML change.

DROP table DEMANDREQUEST
CREATE TABLE DEMANDREQUEST(XMLCOLUMNWHICH NVARCHAR(MAX))
INSERT INTO DEMANDREQUEST VALUES('<Root>
  <Checked>false</Checked>
  <Items>
    <ItemEntity>
      <Checked>true</Checked>
      <FromDate>2013-10-01T00:00:00</FromDate>
      <Id1>604395</Id1>
      <Id2>11278003</Id2>
      <Option>Random1</Option>
      <ToDate>2013-11-01T00:00:00</ToDate>
    </ItemEntity>
    <ItemEntity>
      <Checked>true</Checked>
      <FromDate>2013-07-01T00:00:00</FromDate>
      <Id1>604394</Id1>
      <Id2>11277949</Id2>
      <Option>Random1</Option>
      <ToDate>2013-08-01T00:00:00</ToDate>
    </ItemEntity>
  </Items>
  <EmailNotification>1</EmailNotification>
  <EndDate>2013-11-01T00:00:00</EndDate>
  <Level>crossing</Level>
  <id1>0</id1>
  <Version>0</Version>
</Root>')
-------------------------
DECLARE @handle int
DECLARE @XML xml 
SELECT @XML = XMLCOLUMNWHICH FROM DEMANDREQUEST
EXEC sp_xml_preparedocument @handle out, @XML
SELECT *
FROM openxml(@handle, '/Root/Items/ItemEntity', 2) with (Id1 int, Id2 int)
---------------------------------
SELECT * FROM DEMANDREQUEST