none
Как мне распарсить XML в MS SQL Managment Studio RRS feed

  • Вопрос

  • Здравствуйте,

    У меня возникла проблема написания запроса, который берет данные из БД MSSQL. Вся проблема в том, что справочник адресов системы храниться в таблице данных в столбце с типом text, сам текст это XML срока:

    <newaddress>
    	<mode value="Kladr" />
    		<element value="Россия">
    			<type name="страна" printname="страна" postfix="0" levelET="20" />
    		</element>
    		<element value="Астраханская">
    			<type name="Область" printname="обл" postfix="0" levelET="20" />
    		</element>
    		<element value="Астрахань">
    			<type name="Город" printname="г" postfix="0" levelET="20" />
    		</element>
    		<element value="Черниговская 3-я">
    			<type name="Улица" printname="ул" postfix="0" levelET="20" />
    		</element>
    		<element value="14">
    			<type name="Дом" printname="дом" postfix="0" levelET="20" />
    		</element>
    </newaddress>

    У меня запрос SQL:

    SELECT  EducationLayout.Name, Profession.Name, Address.XML, Count(Entrant.Id) Количество
    FROM         Entrant INNER JOIN
                          Entrant_ReceptionReason ON Entrant.Id = Entrant_ReceptionReason.Id_Entrant INNER JOIN
                          ReceptionReason ON Entrant_ReceptionReason.Id_ReceptionReason = ReceptionReason.Id INNER JOIN
                          ReceptionReason_EducationLayout ON ReceptionReason.Id = ReceptionReason_EducationLayout.Id_ReceptionReason INNER JOIN
                          EducationLayout ON ReceptionReason_EducationLayout.Id_EducationLayout = EducationLayout.Id INNER JOIN
                          ApplicationItem ON Entrant_ReceptionReason.Id = ApplicationItem.Id_EntrantReceptionReason AND 
                          ReceptionReason_EducationLayout.Id = ApplicationItem.Id_ReceptionReason_EducationLayout
                          INNER JOIN
                          Profession ON EducationLayout.Id_Profession = Profession.Id
                          INNER JOIN EntrantResult ON EntrantResult.Id_Entrant=Entrant.Id
                          INNER JOIN ExamBoard ON ExamBoard.Id=EntrantResult.Id_ExamBoard
                          INNER JOIN Address ON Address.Id=Entrant.Id_AddressRegistration
    WHERE     (ApplicationItem.Priority = 1) 
    Group by  EducationLayout.Name, Profession.Name, Address.XML
    ORDER BY EducationLayout.Name

    Естественно при выполнении запроса в строке Address.XML я получаю весь текст, а мне нужно распарсить только Область и Страну, на данном примере получить значения только 'Астраханская' или 'Россия'

    Заранее спасибо.


    Мой секрет успеха заключается в умении понять точку зрения другого человека и смотреть на вещи и с его и со своей точек зрения.

    9 августа 2013 г. 8:34

Ответы

  • вы не верно свою структуру XML описываете:

    declare @xml xml = '<newaddress>
    	<mode value="Kladr" />
    		<element value="Россия">
    			<type name="страна" printname="страна" postfix="0" levelET="20" />
    		</element>
    		<element value="Астраханская">
    			<type name="Область" printname="обл" postfix="0" levelET="20" />
    		</element>
    		<element value="Астрахань">
    			<type name="Город" printname="г" postfix="0" levelET="20" />
    		</element>
    		<element value="Черниговская 3-я">
    			<type name="Улица" printname="ул" postfix="0" levelET="20" />
    		</element>
    		<element value="14">
    			<type name="Дом" printname="дом" postfix="0" levelET="20" />
    		</element>
    </newaddress>'
    
    select a.b.value('@value','varchar(50)') as [Val] 
      from @xml.nodes('newaddress/element') as a(b)


    http://www.t-sql.ru

    13 августа 2013 г. 11:27
    Модератор

Все ответы

  • 9 августа 2013 г. 10:23
  • Спасибо, 

    Сейчас буду пробовать.


    Мой секрет успеха заключается в умении понять точку зрения другого человека и смотреть на вещи и с его и со своей точек зрения.

    13 августа 2013 г. 9:40
  • ;with cteCastToXML as (
        select CAST(Address.XML as xml) as x
            from Address
    )
    select h.ep.value('@value','varchar(50)') as [Val] 
    from cteCastToXML cross apply x.nodes('/newaddress/mode/element') as h(ep)
    Попробовал как в примере, элемент Val пустой возвращается... Не помогло.

    Мой секрет успеха заключается в умении понять точку зрения другого человека и смотреть на вещи и с его и со своей точек зрения.

    13 августа 2013 г. 9:56
  • вы не верно свою структуру XML описываете:

    declare @xml xml = '<newaddress>
    	<mode value="Kladr" />
    		<element value="Россия">
    			<type name="страна" printname="страна" postfix="0" levelET="20" />
    		</element>
    		<element value="Астраханская">
    			<type name="Область" printname="обл" postfix="0" levelET="20" />
    		</element>
    		<element value="Астрахань">
    			<type name="Город" printname="г" postfix="0" levelET="20" />
    		</element>
    		<element value="Черниговская 3-я">
    			<type name="Улица" printname="ул" postfix="0" levelET="20" />
    		</element>
    		<element value="14">
    			<type name="Дом" printname="дом" postfix="0" levelET="20" />
    		</element>
    </newaddress>'
    
    select a.b.value('@value','varchar(50)') as [Val] 
      from @xml.nodes('newaddress/element') as a(b)


    http://www.t-sql.ru

    13 августа 2013 г. 11:27
    Модератор
  • Спасибо!

    Мой секрет успеха заключается в умении понять точку зрения другого человека и смотреть на вещи и с его и со своей точек зрения.

    14 августа 2013 г. 9:07