Retrieve data from XML in SQL Server

Last Reply 2 months ago By pandeyism

Posted 2 months ago

 Hi,

Unable to retrieve data from xml in sql

<DeletedScenarios xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" elementFormDefault="qualified">
    <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
    <xsd:element name="Scenario">
      <xsd:complexType>
        <xsd:sequence>
          <xsd:element name="Sno" type="sqltypes:int" nillable="1" />
          <xsd:element name="Function" nillable="1">
            <xsd:simpleType>
              <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                <xsd:maxLength value="200" />
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
          <xsd:element name="Service_Area" nillable="1">
            <xsd:simpleType>
              <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                <xsd:maxLength value="200" />
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
          <xsd:element name="Service_Line" nillable="1">
            <xsd:simpleType>
              <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                <xsd:maxLength value="200" />
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
          <xsd:element name="CreatedOn" type="sqltypes:date" nillable="1" />
        </xsd:sequence>
      </xsd:complexType>
    </xsd:element>
  </xsd:schema>
  <Scenario xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <Sno>1</Sno>
    <Function>Audit &amp; Assurance</Function>
    <Service_Area>Statutory Audit</Service_Area>
    <Service_Line>Statutory Audit</Service_Line>
    <CreatedOn>2018-09-27</CreatedOn>
  </Scenario>
  <Scenario xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <Sno>2</Sno>
    <Function>Audit &amp; Assurance</Function>
    <Service_Area>Assurance</Service_Area>
    <Service_Line>Integrated Reporting</Service_Line>
    <CreatedOn>2018-09-27</CreatedOn>
  </Scenario>  
</DeletedScenarios>

this is the xml which is stored in a column which datatype is also xml but when i want to retrieve data from below query this is giving null.

declare @xmlString xml
select @xmlString=DeletedScenarioXML from T_DeletedScenarios
SELECT
tbl.col.value('Sno[1]', 'int') AS Sno,
tbl.col.value('Function[1]', 'VARCHAR(MAX)') AS [Function]
FROM @xmlString.nodes('DeletedScenarios/*/Scenario') AS tbl(col)
You are viewing reply posted by: pandeyism 2 months ago.