带有左联接的OPENXML [英] OPENXML with Left Join

问题描述

在SQL Server 2008中，我有2个表分别称为用户"和用户详细信息".主表用户"包含以下列

I have 2 tables called as "User" and "UserDetail" in SQL Server 2008. The main table "User" contains the following columns

UserId Int, 
UserDate DateTime, 
ModifiedBy Int

"UserDetail"表包含以下列

The "UserDetail" table contains the following columns

UserId Int
,UserIdDetailId Int
,Amount Int
,Balance Int

User表包含以下数据

The User table contains data as follows

 UserId UserDate    ModifiedBy
 101  "01-Jan-2016" 100
 102  "01-Feb-2016" 200
 103  "01-Mar-2016" 300

UserDetail表包含的数据为

The UserDetail table contains data as

 UserId UserDetailId Amount Balance
 101  1001  10 2
 102  1002  20 4

对于User表的第三行，UserDetail表中没有子记录.

For 3rd row of the User table, it does not have child records in UserDetail table.

现在我想使用sp从User和UserDetail表中检索记录

Now I want to retrieve records from both User and UserDetail table using sp

在前端asp.net中，我将xml框架化并将其传递给sql sp.

In front end asp.net, I frame the xml and pass it to sql sp.

<root>
<rec UserId="101" UserDate="8/22/2016 12:00:00 AM" ModifiedBy="8/22/2016 01:10:59 PM" userdetails="System.Collections.Generic.List`1[UserDetail]">
    <userdetail userdetailid="1001" amount="10" balance="2"  />
</rec>
<rec userid="102" userdate="8/22/2016 12:00:00 AM" modifiedby="8/22/2016 01:10:59 PM" userdetails="System.Collections.Generic.List`1[UserDetail]">
    <userdetail userdetailid="1001" amount="1=20" balance="4" />
</rec>
<rec userid="103" userdate="8/22/2016 12:00:00 AM" modifiedby="8/22/2016 01:10:59 PM" userdetails="System.Collections.Generic.List`1[UserDetail]" />

sql sp中的openxml如下

The openxml in sql sp is as follows

SELECT *
INTO #temp
FROM (
SELECT  m.UserId,
    m.UserDate,
    m.ModifiedBy,
    COALESCE(det.UserDetailId, -1) as UserDetailId
FROM OPENXML(@i, '/Root/Rec', 1) WITH (
   ,UserId INT '@UserId'
   ,UserDate DATETIME '@UserDate'
   ,ModifiedBy INT '@ModifiedBy'
) as m
LEFT JOIN (
SELECT  UserId,
        UserDetailId
FROM OPENXML(@i, '/Root/Rec/UserDetail', 1) WITH (
         UserId INT '@UserId'
        ,UserIdDetailId INT '@UserIdDetailId'
    ,Amount INT '@Amount'
    ,Balance INT '@Balance'
        )
) as det
ON m.UserId =det.UserId

)p

我希望它按如下方式存储在#temp表中

I want it to store in #temp table as follows

UserId UserDetailId  UserDate       ModifiedBy Amount Balance
101    1001          "01-Jan-2016"  100        10     2
102    1002          "01-Feb-2016"  200        20     4
103    1003          "01-Mar-2016"  300        NULL   NULL

我也希望在此表中也获取103 UserId，并使用NULL作为详细值

I want to get 103 UserId also in this table with NULL for detail values

我的问题是，如何使用OPENXML的联接来构造OPENXML以便从User和UserDetail表中获取所有记录，我对OPENXML及其联接不满意，但是我希望以某种方式使其能够正确执行.

My Question is how the OPENXML must be framed to get all the records from User and UserDetail tables using joins in OPENXML and I am not comfortable with OPENXML and its joins but somehow I want this to execute properly.

推荐答案

首先添加 Amount 和 Balance ，因此您可以选择以下字段:

At first add Amount and Balance, so you can select this fields:

SELECT *
INTO #temp
FROM (
    SELECT  m.UserId,
            m.UserDate,
            m.ModifiedBy,
            COALESCE(det.UserDetailId, -1) as UserDetailId,
            det.Amount,
            det.Balance
    FROM OPENXML(@i, '/Root/Rec', 1) WITH (
       UserId INT '@UserId',
       UserDate DATETIME '@UserDate',
       ModifiedBy INT '@ModifiedBy'
) as m
LEFT JOIN (
SELECT  UserId,
        UserDetailId,
        Amount,
        Balance
FROM OPENXML(@i, '/Root/Rec/UserDetail', 1) WITH (
    UserId INT '../@UserId',
    userdetailid INT '../@UserDetailId',
    Amount INT '@Amount',
    Balance INT '@Balance'
    )
) as det
    ON m.UserId =det.UserId
) as p

关于您的问题，尚不清楚您希望从此联接中获得什么输出.

As for your question, it is not clear, what output you want to get from this join.

这篇关于带有左联接的OPENXML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！