none
SELECT query in SQL XML Data Type

    Question

  • I have field in DB where we have one field of xml data type
    <Root>
    <
    TestsSafetyItems>
    <UnitId>AA986137</UnitId>
    <
    ItemId>13</ItemId>
    <
    Cost>0</Cost>
    </
    TestsSafetyItems>
    <TestsSafetyItems>
    <UnitId>AA986137</UnitId>
    <
    ItemId>14</ItemId>
    <
    Cost>0</Cost>
    </
    TestsSafetyItems>
    <TestsSafetyItems>
    <UnitId>AA986137</UnitId>
    <
    ItemId>15</ItemId>
    <
    Cost>0</Cost>
    </
    TestsSafetyItems>
    </Root>

    How can we Fire SELECT Query to retieve the all values in table format if the above data is store in xml data type field

     

     

     

    Thursday, March 01, 2007 2:04 PM

Answers

  • You could use xml.nodes function split xml into table:

    create table #xml_temp (

    xml_col xml

    )

    insert into #xml_temp values('

    <Root>

    <TestsSafetyItems>

    <UnitId>AA986137</UnitId>

    <ItemId>13</ItemId>

    <Cost>0</Cost>

    </TestsSafetyItems>

    <TestsSafetyItems>

    <UnitId>AA986137</UnitId>

    <ItemId>14</ItemId>

    <Cost>0</Cost>

    </TestsSafetyItems>

    <TestsSafetyItems>

    <UnitId>AA986137</UnitId>

    <ItemId>15</ItemId>

    <Cost>0</Cost>

    </TestsSafetyItems>

    </Root>')

     

    select

    xmlt.col.value('UnitId[1]','varchar(10)') AS UnitId

    ,xmlt.col.value('ItemId[1]','int') AS ItemId

    ,xmlt.col.value('Cost[1]','decimal') AS Cost from

    #xml_temp

    cross apply xml_col.nodes('/Root/TestsSafetyItems') xmlt(col)

    Thursday, March 01, 2007 3:11 PM

All replies

  • You could use xml.nodes function split xml into table:

    create table #xml_temp (

    xml_col xml

    )

    insert into #xml_temp values('

    <Root>

    <TestsSafetyItems>

    <UnitId>AA986137</UnitId>

    <ItemId>13</ItemId>

    <Cost>0</Cost>

    </TestsSafetyItems>

    <TestsSafetyItems>

    <UnitId>AA986137</UnitId>

    <ItemId>14</ItemId>

    <Cost>0</Cost>

    </TestsSafetyItems>

    <TestsSafetyItems>

    <UnitId>AA986137</UnitId>

    <ItemId>15</ItemId>

    <Cost>0</Cost>

    </TestsSafetyItems>

    </Root>')

     

    select

    xmlt.col.value('UnitId[1]','varchar(10)') AS UnitId

    ,xmlt.col.value('ItemId[1]','int') AS ItemId

    ,xmlt.col.value('Cost[1]','decimal') AS Cost from

    #xml_temp

    cross apply xml_col.nodes('/Root/TestsSafetyItems') xmlt(col)

    Thursday, March 01, 2007 3:11 PM
  • Thanks Konstantin Kosinsky

    I am really thank full to you .Your post had solved lot of my problems and helpful to gain knowledge too..

     

    Monday, March 05, 2007 7:15 AM
  • hi

    actuaaly i have prob , i have a XML file on my PC Drive , and from that file i want to insert the data in  SQL table

    File name is DLCLocator.XML has the Data

     

     

    <Persistance>
      <xsTongue Tiedchema id="Persistance" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urnTongue Tiedchemas-microsoft-com:xml-msdata">
        <xs:element name="Persistance" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
          <xs:complexType>
            <xs:choice minOccurs="0" maxOccurs="unbounded">
              <xs:element name="Table">
                <xs:complexType>
                  <xsTongue Tiedequence>
                    <xs:element name="ImageName" type="xsTongue Tiedtring" minOccurs="0" />
                    <xs:element name="Location" type="xsTongue Tiedtring" minOccurs="0" />
                    <xs:element name="Make" type="xsTongue Tiedtring" minOccurs="0" />
                    <xs:element name="Model" type="xsTongue Tiedtring" minOccurs="0" />
                    <xs:element name="Year" type="xsTongue Tiedhort" minOccurs="0" />
                  </xsTongue Tiedequence>
                </xs:complexType>
              </xs:element>
            </xs:choice>
          </xs:complexType>
        </xs:element>
      </xsTongue Tiedchema>
      <Table>
        <ImageName>CL99 copy.jpg</ImageName>
        <Location>center console covered</Location>
        <Make>Acura</Make>
        <Model>CL</Model>
        <Year>1997</Year>
      </Table>
      <Table>
        <ImageName>CL99 copy.jpg</ImageName>
        <Location>center console covered</Location>
        <Make>Acura</Make>
        <Model>CL</Model>
        <Year>1998</Year>
      </Table>
      <Table>
        <ImageName>CL99 copy.jpg</ImageName>
        <Location>center console covered</Location>
        <Make>Acura</Make>
        <Model>CL</Model>
        <Year>1999</Year>
      </Table>
      <Table>
        <ImageName>TL99 copy.jpg</ImageName>
        <Location>center dash above shifter</Location>
        <Make>Acura</Make>
        <Model>CL</Model>
        <Year>2001</Year>
      </Table>
      <Table>
        <ImageName>tl03 copy.jpg</ImageName>
        <Location>drivers side</Location>
        <Make>Acura</Make>
        <Model>CL</Model>
        <Year>2002</Year>
      </Table>
      <Table>
        <ImageName>tl03 copy.jpg</ImageName>
        <Location>drivers side</Location>
        <Make>Acura</Make>
        <Model>CL</Model>
        <Year>2003</Year>
      </Table>
      <Table>
        <ImageName>tl03 copy.jpg</ImageName>
        <Location>drivers side</Location>
        <Make>Acura</Make>
        <Model>CL</Model>
        <Year>2004</Year>
      </Table>
      <Table>
        <ImageName>tl03 copy.jpg</ImageName>
        <Location>drivers side</Location>
        <Make>Acura</Make>
        <Model>CL</Model>
        <Year>2005</Year>
      </Table>
      <Table>
        <ImageName>integra96 copy.jpg</ImageName>
        <Location>passenger side</Location>
        <Make>Acura</Make>
        <Model>Integra</Model>
        <Year>1996</Year>
      </Table>
      <Table>
        <ImageName>integra96 copy.jpg</ImageName>
        <Location>passenger side</Location>
        <Make>Acura</Make>
        <Model>Integra</Model>
        <Year>1997</Year>
      </Table>
      <Table>
        <ImageName>integra99 copy.jpg</ImageName>
        <Location>passenger side</Location>
        <Make>Acura</Make>
        <Model>Integra</Model>
        <Year>1998</Year>
      </Table>
    </Persistance>

    Friday, June 08, 2007 10:02 AM