TSQL Shred XML - 这是正确的还是有更好的方法(新手@shredding XML) [英] TSQL Shred XML - Is this right or is there a better way (newbie @ shredding XML)

问题描述

好的，我是 C# ASP.NET 开发人员，遵循以下命令:这些命令是获取给定的数据集、分解 XML 并返回列.我认为在 ASP.NET 端进行粉碎更容易，因为我们已经可以访问反序列化器等，以及已知类型的整个复合体，但不，老板说在服务器上粉碎它，返回一个数据集，将数据集绑定到 gridview 的列"，所以现在，我正在做我被告知的事情.这一切都是为了阻止那些会过来说糟糕的要求"的人.

Ok, I'm a C# ASP.NET dev following orders: The orders are to take a given dataset, shred the XML and return columns. I've argued that it's easier to do the shredding on the ASP.NET side where we already have access to things like deserializers, etc, and the entire complex of known types, but no, the boss says "shred it on the server, return a dataset, bind the dataset to the columns of the gridview" so for now, I'm doing what I was told. This is all to head off the folks who will come along and say "bad requirements".

这是我的代码，它可以正常工作并执行我想要的操作:

Here's my code that works and does what I want it to:

DECLARE @table1 AS TABLE (
    ProductID    VARCHAR(10)
  , Name         VARCHAR(20)
  , Color        VARCHAR(20)
  , UserEntered  VARCHAR(20)
  , XmlField     XML
)

INSERT INTO @table1 SELECT '12345','ball','red','john','<sizes><size name="medium"><price>10</price></size><size name="large"><price>20</price></size></sizes>'
INSERT INTO @table1 SELECT '12346','ball','blue','adam','<sizes><size name="medium"><price>12</price></size><size name="large"><price>25</price></size></sizes>'
INSERT INTO @table1 SELECT '12347','ring','red','john','<sizes><size name="medium"><price>5</price></size><size name="large"><price>8</price></size></sizes>'
INSERT INTO @table1 SELECT '12348','ring','blue','adam','<sizes><size name="medium"><price>8</price></size><size name="large"><price>10</price></size></sizes>'
INSERT INTO @table1 SELECT '23456','auto','black','ann','<auto><type>car</type><wheels>4</wheels><doors>4</doors><cylinders>3</cylinders></auto>'
INSERT INTO @table1 SELECT '23457','auto','black','ann','<auto><type>truck</type><wheels>4</wheels><doors>2</doors><cylinders>8</cylinders></auto><auto><type>car</type><wheels>4</wheels><doors>4</doors><cylinders>6</cylinders></auto>'

DECLARE @x XML
SELECT @x = (
    SELECT 
        ProductID
      , Name
      , Color
      , UserEntered
      , XmlField.query('
            for $vehicle in //auto
            return <auto 
                type = "{$vehicle/type}"
                wheels = "{$vehicle/wheels}"
                doors = "{$vehicle/doors}"
                cylinders = "{$vehicle/cylinders}"
            />')
    FROM @table1 table1
    WHERE Name = 'auto'
    FOR XML AUTO
)

SELECT @x

SELECT 
    ProductID    = T.Item.value('../@ProductID', 'varchar(10)')
  , Name         = T.Item.value('../@Name', 'varchar(20)')
  , Color        = T.Item.value('../@Color', 'varchar(20)')
  , UserEntered  = T.Item.value('../@UserEntered', 'varchar(20)')
  , VType        = T.Item.value('@type' , 'varchar(10)')
  , Wheels       = T.Item.value('@wheels', 'varchar(2)')
  , Doors        = T.Item.value('@doors', 'varchar(2)')
  , Cylinders    = T.Item.value('@cylinders', 'varchar(2)')
FROM   @x.nodes('//table1/auto') AS T(Item)

SELECT @x = (
    SELECT 
        ProductID
      , Name
      , Color
      , UserEntered
      , XmlField.query('
            for $object in //sizes/size
            return <size 
                name = "{$object/@name}"
                price = "{$object/price}"
            />')
    FROM @table1 table1
    WHERE Name IN ('ring', 'ball')
    FOR XML AUTO
)

SELECT @x

SELECT 
    ProductID    = T.Item.value('../@ProductID', 'varchar(10)')
  , Name         = T.Item.value('../@Name', 'varchar(20)')
  , Color        = T.Item.value('../@Color', 'varchar(20)')
  , UserEntered  = T.Item.value('../@UserEntered', 'varchar(20)')
  , SubName        = T.Item.value('@name' , 'varchar(10)')
  , Price       = T.Item.value('@price', 'varchar(2)')
FROM   @x.nodes('//table1/size') AS T(Item)

所以现在，我想弄清楚是否有比我现在正在做的更好的编写代码的方法......(我有一个 第 2 部分 与这个一起......)

So for now, I'm trying to figure out if there's a better way to write the code than what I'm doing now... (I have a part 2 to go along with this one...)

推荐答案

在服务器上分解 XML 还是在客户端分解 XML 是好是坏取决于多种因素，这些要求可能完全有效.鉴于 SQL Server 2005 及之后对 XML(XPath/XQuery/XML 索引)的广泛支持，在服务器上分解 XML 通常是一种非常明智的方法.

Whether shredding the XML on the server as opposed to doing it on the client is good or bad depends on a variety of factors, the requirements may be perfectly valid. Shredding XML on the server, given the extensive support SQL server 2005 and after have for XML (XPath/XQuery/XML indexes) is often a very sensible approach.

但是，您在帖子中提供的是使用 XML 对数据进行语义建模的示例.我建议您阅读几份白皮书:

However, what you have in your post is an example of semantic modeling of data, using XML. I recommend you go over a couple of white papers:

• 语义数据建模的性能和可扩展性最佳实践
• Microsoft SQL Server 2005 的 XML 最佳实践
• SQL Server 2005 中 XML 数据类型的性能优化
• 在 SQL Server 中使用 XML 数据的性能技巧

我不知道你例子中的@table1是只是一个例子还是你在生产中使用的实际数据结构，但有些观点在你阅读那些论文后会立即跳出来:

I don't know if the @table1 in your example is just an example or the actual data structure you use in production, but some points will jump out immediately after you read those papers:

• 尽可能使用类型化 XML(添加架构)
• 使用适当的 XML 索引进行您需要的处理
• 尝试在一次转换中分解所有 XML，而不是连续 3 步

最后，如果您需要每次查询时都需要细化，也许您需要分析数据模型(这是我列表中的第一篇论文有用的地方).

And finally, if you need to shred every time you query, perhaps you need to analyze the data model (this is where the first paper in my list is useful).

这篇关于TSQL Shred XML - 这是正确的还是有更好的方法(新手@shredding XML)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！