none
Extract Values from XML column of SQL

    Question

  • 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


    Saturday, July 06, 2013 8:28 PM

Answers

  • <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);


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    Saturday, July 06, 2013 8:53 PM

All replies

  • <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);


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    Saturday, July 06, 2013 8:53 PM
  • 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


    Regards, RSingh

    Sunday, July 07, 2013 3:14 AM