Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Вопрос по XML  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Всем добрый день!

Есть XML:
<ProductInfo>
  <Products>
    <Product Code="630699" />
    <Product Code="630700" />
    <Product Code="630701">
      <Set>
        <Product Code="630699" />
        <Product Code="630800" />
        <Product Code="630801">
          <Set>
            <Product Code="630699" />
            <Product Code="630859" />
          </Set>
        </Product>
      </Set>
    </Product>
    <Product Code="630702" />
  </Products>
</ProductInfo>


Как мне вернуть узлы с полным путём, например для Code="630699" ?
Т.е. ответ должен быть:
<ProductInfo>
  <Products>
    <Product Code="630699" />
  </Products>
</ProductInfo>


<ProductInfo>
  <Products>
    <Product Code="630701">
      <Set>
        <Product Code="630699" />
      </Set>
    </Product>
  </Products>
</ProductInfo>


<ProductInfo>
  <Products>
    <Product Code="630701">
      <Set>
        <Product Code="630801">
          <Set>
            <Product Code="630699" />
          </Set>
        </Product>
      </Set>
    </Product>
  </Products>
</ProductInfo>
15 мар 12, 15:12    [12253966]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по XML  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Афигеть структура!!! О чём чел думал когда её придумывал. Есть подозрение, что оно получилось от нашарного запроса.

А что вам надо в итоге, а то часто бывает, что тернистый путь который себе представляют делается в один однострочный запрос.
Вохможно SqlXmlBulkLoad сможет этот граф запендёрить в таблу махом.

Ошибка в том что такая структура (помимо убогой наглядности) позволяет делать ошибочные вещи - повторять состав одного и тогоже продукта в нескольких местах, что судя по Xml запрещено.
На вашем месте утопил бы проэктировщика данного XML в лучах ненавести и заставил всё это переделать в двух уровневый Xml, без выкрутасов.


+ А это чтоб не возмущались (из-за неправильного восприятия реальности)
DECLARE	@XML XML = '<ProductInfo>
  <Products>
    <Product Code="630699" />
    <Product Code="630700" />
    <Product Code="630701">
      <Set>
        <Product Code="630699" />
        <Product Code="630800" />
        <Product Code="630801">
          <Set>
            <Product Code="630699" />
            <Product Code="630859" />
          </Set>
        </Product>
      </Set>
    </Product>
    <Product Code="630702" />
  </Products>
</ProductInfo>'

;WITH Product (ID,Parent) AS (
	SELECT	 Product.X.value('@Code','Int')
		,Product.X.value('./../../@Code','Int')
	FROM	@XML.nodes('//Product')Product(X)
),Tree (X,Parent,[Root]) AS (
	SELECT	 (SELECT ID AS [@Code] FOR XML Path('Product'),Type)
		,Parent
		,ID
	FROM	Product
UNION ALL
	SELECT	 (SELECT ID AS [@Code],T.X AS [Set] FOR XML Path('Product'),Type)
		,P.Parent
		,T.[Root]
	FROM	Tree T JOIN Product P ON P.ID = T.Parent
)	SELECT	(SELECT X AS [Products] FOR XML Path('ProductInfo'),Type)
	FROM	Tree
	WHERE	    Parent IS NULL
		AND [Root] = 630699
16 мар 12, 11:10    [12258590]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по XML  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Mnior,

Спасибо, Ваше знание XML всегда поражает.

А в структуре ничего сверхъестественного.
XML-документ представляет список продуктов в магазине.
Допустим, продукт с Code="630699" - это отвёртка.
Она может продаваться отдельно, в составе набора отвёрток (тег <Set>), набор отвёрток может входить в набор инструментов для монтажа и т.п.
16 мар 12, 14:42    [12260625]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по XML  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Jovanny
Допустим, продукт с Code="630699" - это отвёртка.
Она может продаваться отдельно, в составе набора отвёрток (тег <Set>), набор отвёрток может входить в набор инструментов для монтажа и т.п.
В том то и прикол. Хватает два уровня:
Продукт ... бла, бла ... Состоит из: список ID продуктов.

А у вас дерево.
Продукт ... бла, бла ... Состоит из: Список:
- Продукт ... бла, бла ... Состоит из: ...

Единственное объяснение пришедшее на ум, что мол есть вещи которые составные, но не продаются отдельно.
В двух уровневой системе было бы проще пометить тэгом "не продаётся отдельно" и всё.
А так могут быть курьёзы типа:
<ProductInfo>
  <Products>
    <Product Code="1">
      <Set>
        <Product Code="2">
          <Set>
            <Product Code="3" />
          </Set>
        </Product>
      </Set>
    </Product>
    <Product Code="2">
      <Set>
        <Product Code="4" />
      </Set>
    </Product>
  </Products>
</ProductInfo>
Это как единовременно одна и таже сущность (2) состоит из взаимоисключающих элементов (3,4)?

iPad одной и той же партии "ABC" в Москву ушёл с чипами A1, а в Питер с В2.
Или партия другая или кто-то перемудрил в структуре XML или "состоит из" имеет другой смысл (т.е. Code лишний).
16 мар 12, 18:23    [12262828]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по XML  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Mnior,

Целиком с Вами согласен.
Прикол в том, что я процедурами разбираю такой XML и сохраняю в реляционном виде, где главная таблица состоит из двух полей: ProductId и MaterialId(составные части).
16 мар 12, 18:51    [12262958]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по XML  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
Правильно ли так, но точно объемнее решения Mnior-а
+
DECLARE @x XML = N'
<ProductInfo>
 <Products>
  <Product Code="630699" />
  <Product Code="630700" />
  <Product Code="630701">
   <Set>
    <Product Code="630699" />
    <Product Code="630800" />
    <Product Code="630801">
     <Set>
      <Product Code="630699" />
      <Product Code="630859" />
      </Set>
     </Product>
    </Set>
   </Product>
  <Product Code="630702" />
 </Products>
</ProductInfo>';
WITH un AS (
SELECT
 0 AS pl,1 AS l
,0 AS pid,CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS INTEGER) AS id
,CAST(NULL AS XML) AS px,ns.x.query('.') AS x
,CAST(NULL AS NVARCHAR(MAX)) AS pn
,ns.x.value('local-name(.)','nvarchar(max)') AS n
,ns.x.value('@Code','INTEGER') AS cd
FROM @x.nodes('node()') AS ns(x)
UNION ALL
SELECT
 l,l+1
,id,CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS INTEGER)
,un.x,ns.x.query('.')
,n,ns.x.value('local-name(.)','nvarchar(max)')
,ns.x.value('@Code','INTEGER')
FROM un CROSS APPLY un.x.nodes('node()/node()') AS ns(x)
), ne AS (
SELECT
 pl,pid,pn,px--,x
,LEFT(CAST(un.px AS NVARCHAR(MAX))
     ,CHARINDEX('>',CAST(un.px AS NVARCHAR(MAX))))
+CAST(un.x AS NVARCHAR(MAX))+'</'+un.pn+'>' AS s
FROM un
WHERE cd = 630699
UNION ALL
SELECT
 un.pl,un.pid,un.pn,un.px--,un.x
,LEFT(CAST(un.px AS NVARCHAR(MAX))
     ,CHARINDEX('>',CAST(un.px AS NVARCHAR(MAX))))
+ne.s+'</'+un.pn+'>'
FROM un JOIN ne on un.l=ne.pl AND un.id=ne.pid)
SELECT
 CAST(s AS XML) AS x
FROM ne
WHERE pl=1
--OPTION (MAXRECURSION 0)
;
18 мар 12, 09:28    [12268417]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по XML  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
Слегка допилил
+
DECLARE @x XML = N'
<ProductInfo>
 <Products>
  <Product Code="630699" />
  <Product Code="630700" />
  <Product Code="630701">
   <Set>
    <Product Code="630699" />
    <Product Code="630800" />
    <Product Code="630801">
     <Set>
      <Product Code="630699" />
      <Product Code="630859" />
      </Set>
     </Product>
    </Set>
   </Product>
  <Product Code="630702" />
 </Products>
</ProductInfo>';
WITH un AS (
SELECT
 0 AS pl,1 AS l
,0 AS pid,CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS INTEGER) AS id
,CAST(NULL AS XML) AS px,ns.x.query('.') AS x
,CAST(NULL AS NVARCHAR(MAX)) AS pn
,ns.x.value('local-name(.)','NVARCHAR(MAX)') AS n
--,ns.x.value('@Code','INTEGER') AS cd
FROM @x.nodes('*') AS ns(x)
UNION ALL
SELECT
 l,l+1
,id,CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS INTEGER)
,un.x,ns.x.query('.')
,n,ns.x.value('local-name(.)','NVARCHAR(MAX)')
--,ns.x.value('@Code','INTEGER')
FROM un CROSS APPLY un.x.nodes('*/*') AS ns(x)
), ne AS (
SELECT
 pl,pid,pn,px--,x
,LEFT(CAST(un.px AS NVARCHAR(MAX))
     ,CHARINDEX('>',CAST(un.px AS NVARCHAR(MAX))))
+CAST(un.x AS NVARCHAR(MAX))+'</'+un.pn+'>' AS s
FROM un
--WHERE cd = 630699
UNION ALL
SELECT
 un.pl,un.pid,un.pn,un.px--,un.x
,LEFT(CAST(un.px AS NVARCHAR(MAX))
     ,CHARINDEX('>',CAST(un.px AS NVARCHAR(MAX))))
+ne.s+'</'+un.pn+'>'
FROM un JOIN ne on un.l=ne.pl AND un.id=ne.pid)
SELECT
 CAST(s AS XML) AS x
FROM ne
WHERE pl=1
AND CAST(s AS XML).exist('//Product[@Code=630699 and count(../*)=1 and not(*)]')=1
--OPTION (MAXRECURSION 0)
;
18 мар 12, 18:32    [12269671]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по XML  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
andrey odegov,

Спасибо, запрос действительно не тривиальный.
20 мар 12, 10:46    [12279333]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить