دستور For XML برای بازگرداندن نتیجه کوئری SQL به شکل XML

استفاده از عبارت یا دستور For XML به علت شرح محدود در Book-on-Line ممکن است در عین سادگی سبب مشکل شود، با توجه به اینکه امروزه اکثر سازمان های دولتی جهت دریافت خروجی از شرکت ها از این فرمت استفاده می کنند لازم دیدم به صورت مفصل طی یک مقاله استفاده از آن را شرح دهم.

SQL Server به شما اجازه می دهد که اطلاعات بازیابی شده از بانک را با کمک عبارت For Xml به شکل Xml دریافت کنید. این عبارت از تعداد زیادی حالت یا تنظیم انتخابی متنوع پشتیبانی می کند.

وقتی که از عبارت For XML در انتهای کوئری خود استفاده می کنید بایستی یکی از چهار حالت پشتیبانی شده Raw, AUTO, Explicit و یا PATH را انتخاب کنید. تنظیمات متفاوتی با توجه به انتخاب هر یک از حالت های ذکر شده در دسترس می باشد.

هر یک از این چهار حالت (Mode) به تفصیل شرح داده می شود:

حالت Raw Mode

حالت Raw یک المنت XML برای هر رکورد موجود در حاصل کوئری تولید می کند.

برای استفاه از این حالت کافیست که کلید واژه Raw را در انتهای کوئری مطابق نمونه زیر اضافه کنید

SELECT e.BusinessEntityID, c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Person c

   ON c.BusinessEntityID = e.BusinessEntityID

'WHERE c.FirstName = 'Rob

;FOR XML RAW

توجه کنید که دستور اجرا شده بدون عبارت For XML Raw یک کوئری ساده Sql Server روی بانک AdventureWorks  است که مشخصات اشخاص با نام کوچک Bob را باز میگرداند که در زیر می بینید:

با افزودن عبارت For XML RAW حاصل به فرم زیر خواهد بود

</ "row BusinessEntityID="130" FirstName="Rob" MiddleName="T" LastName="Caron>

</ "row BusinessEntityID="130" FirstName="Rob"  LastName="Walters>

همانطور که می بینید هر <row>  معادل یکی از رکوردهای موجود در جدول می باشد که طی دستور Select بازگردانده می شوند و هم چنین هر ستون به عنوان یکی از Attribute های آن عنصر (المنت) محسوب می شوند.

توجه کنید که عبارت For XML فقط برای کوئری Select قابل استفاده است

در مثالی که بالاتر ذکر شد هر عنصر یا المنت به صورت پیش فرض در نتیجه حاصل Xml با نام <row> ساخته شده بود. اگر چه می توان این رفتار پیش فرض را تغییر داده و از نام دلخواه خود برای عناصر حاصل در نتیجه استفاده کنید:

در مثال زیر نام عنصر به Employee تغییر داده شده:

SELECT e.BusinessEntityID, c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Person c

ON c.BusinessEntityID = e.BusinessEntityID

'WHERE c.FirstName = 'Rob

;('FOR XML RAW ('Employee

با اجرای کوئری جدید هر عنصر (المنت) مربوط به رکورد های جدول با نام جدید <Employee> نمایش داده می شود :

</ "Employee BusinessEntityID="130" FirstName="Rob" MiddleName="T" LastName="Caron>

</ "Employee BusinessEntityID="130" FirstName="Rob"  LastName="Walters>

برای افزودن عنصر ریشه Root Element کافیست که عبارت Root را به انتهای دستور اضافه کنید، هم چنین برای اینکه عنصر ریشه نیز با نام دلخواه شما باشد می توانید مشابه انتخاب نام برای هر المنت برای ریشه نیز یک نام دلخواه انتخاب کنید

SELECT e.BusinessEntityID, c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Person c

ON c.BusinessEntityID = e.BusinessEntityID

'WHERE c.FirstName = 'Rob

;('FOR XML RAW ('Employee') , Root('Employees

توجه کنید که برای افزودن هر تنظیم جدید کاما را فراموش نکنید. پس از اجرای کوئری نتیجه به شکل زیر است: 

<Employees>

</ "Employee BusinessEntityID="130" FirstName="Rob" MiddleName="T" LastName="Caron>

</ "Employee BusinessEntityID="130" FirstName="Rob"  LastName="Walters>

<Employees/>

تا اینجا خروجی کوئری شامل عنصر ریشه با نام دلخواه و یک المنت به ازای هر رکورد موجود در حاصل کوئری Select است که ستون های آن به شکل Attribute نمایش داده می شوند، این رفتار پیش فرض Raw Mode می باشد البته می توان هر کدام از ستون های موجود در حاصل Select را به صورت یک فرزند Child برای عنصر Row نمایش داد.

با افزودن کلمه کلیدی Elements کوئری بدین گونه تغییر می کند:

SELECT e.BusinessEntityID, c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Person c

ON c.BusinessEntityID = e.BusinessEntityID

'WHERE c.FirstName = 'Rob

;FOR XML RAW ('Employee') , Root('Employees'), ELEMENTS  

حاصل اجرای کوئری فوق هر رکورد شامل فرزندانی است که ستون های جدول متناظر را تشکیل می دهند:

<Employees>

  <Employee>

    <BusinessEntityID>4</BusinessEntityID>

    <FirstName>Rob</FirstName>

    <LastName>Walters</LastName>

  </Employee>

  <Employee>

    <BusinessEntityID>130</BusinessEntityID>

    <FirstName>Rob</FirstName>

    <MiddleName>T</MiddleName>

    <LastName>Caron</LastName>

  </Employee>

<Employees/>

با این کلمه کلیدی عنصر <Employee> شامل Attribute نیست و کلی دیتای درون عناصری با نام همان ستون در بانک تولید شده اند.

اگر به خروجی های نمایش داده شده تاکنون دقت کرده باشید برای فیلد MiddleName رکورد با شناسه  4 هیچ Attribute و یا عنصری تولید نشده بود زیرا مقدار این ستون برای آن null می باشد. می توان تنظیماتی اعمال کرد که این رفتار تغییر یافته و عناصر مربوط به فیلد های null نیز در خروجی آورده شوند.

برای این کار کافیست کلمه کلیدی XSINIL نیز به کوئری اضافه گردد:

SELECT e.BusinessEntityID, c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Person c

   ON c.BusinessEntityID = e.BusinessEntityID

WHERE c.FirstName = 'Rob'

;FOR XML RAW('Employee'),ROOT('Employees'),ELEMENTS XSINIL

اکنون حاصل شامل عناصر مربوط به سلول های حاوی null در بانک می باشد که خصیصه xsi:nil با مقدار true  می باشد: 

< "Employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance>

  <Employee>

    <BusinessEntityID>4</BusinessEntityID>

    <FirstName>Rob</FirstName>

</ "MiddleName xsi:nil="true>   

<LastName>Walters</LastName>

  </Employee>

  <Employee>

    <BusinessEntityID>130</BusinessEntityID>

    <FirstName>Rob</FirstName>

    <MiddleName>T</MiddleName>

    <LastName>Caron</LastName>

  </Employee>

<Employees/>

توجه کنید که خصیصه xmlns:xsi به عنصر ریشه نیز افزوده شده است که فرآهم کننده  اسکیمای پیش فرض می باشد.

با افزودن کلمه کلیدی XMLSCHEMA در حالت Raw نیز W3C XML Schema یا XSD به شکل کامل برای کلیه عناصر نمایش داده می شود.

حالت Auto Mode

حاصل تولید شده در حالت Auto Mode با Raw از این جنبه متفاوت است که به صورت پیش فرض  مجموعه ای از نتایج را تولید می کند. در واقع حالت  Auto یک فرآیند اکتشافی (heuristics) را بر روی نحوه ایجاد   دستور Select  اجرا می کند، برای درک بهتر نحوه کار این حالت یک مثال را بررسی می کنیم:

SELECT Employee.BusinessEntityID,PersonInfo.FirstName,PersonInfo.MiddleName, PersonInfo.LastName
FROM HumanResources.Employee AS Employee
   INNER JOIN Person.Person AS PersonInfo
   ON PersonInfo.BusinessEntityID = Employee.BusinessEntityID
WHERE PersonInfo.FirstName = 'Rob'
;('FOR XML AUTO, ROOT ('Employees

حاصل دستور مشابه دستور قبل را روی بانک AdventureWorks  با کلمه کلیدی Auto می بینیم که البته با کمک نام Alias اسامی با معنی به عناصر تولید شده توسط Auto داده شده است:

<Employees>

  <"Employee EmployeeID="4>

    </ "ContactInfo FirstName="Rob" LastName="Walters>

  </Employee>

  <"Employee EmployeeID="168>

    </ "ContactInfo FirstName="Rob" MiddleName="T" LastName="Caron>   

  </Employee>

</Employees>

همانطور که می بینید عناصر تولید شده براساس جداول موجود در کوئری ایجاد شده و نام آنها نیز از Alias های برداشته شده است، هم چنین ستون های مربوط به هر جدول به شکل Attribute های مختص همان عنصر نمایش داده شوند که به راحتی می توان هر ستون مربوط به کدام جدول بوده است.

کلمات کلیدی Elements و XSINIL تنظیمات مشابه حالت Raw Mode را بر روی حالت Auto اعمال می کنند. باید توجه داشت که حالت Auto بر اساس ترتیبی که فیلد های انتخاب شده در کوئری آمده ست ممکن است چیدمان XML خروجی را تغییر دهد در نتیجه بایستی به ترتیب آمدن ستون ها دقت شود.

در صورتی که بخواهیم نتیجه خروجی در بخش هایی ستون ها به صورت Attribute و در بخش هایی به صورت Element نمایش داده شوند. می توان کوئری اولیه را به صورت دو SubQuery نوشته و هر کدام را با تنظیمات متفاوت خروجی بگیریم. نمایش یک مثال می تواند گویا تر باشد:

SELECT BusinessEntityID, LoginID,   (SELECT FirstName, LastName
    FROM Person.Person AS EmployeeName
    WHERE EmployeeName.BusinessEntityID = Employee.BusinessEntityID
    FOR XML AUTO, TYPE, ELEMENTS)
FROM HumanResources.Employee AS Employee
WHERE BusinessEntityID = 168
;FOR XML AUTO

توجه کنید که SubQuery شامل کلمه کلیدی ElEMENTS می باشد که باعث می شود ستون ها به شکل عنصر مجزای فرزند نمایش داده شوند. هم چنین کلمه کلیدی TYPE نیز مشخص می کند که نتیجه  SubQuery بایستی به شکل XML بازگردانده شود. 

کوئری خارجی نیز شامل For XML Auto است البته بدون ELEMENTS که باعث می شود ستون ها به شکل Attribute نمایش داده شوند، حاصل اجرای کوئری در زیر نمایش داده شده است:

<"Employee BusinessEntityID="168" LoginID="adventure-works\garrett0>

  <EmployeeName>

    <FirstName>Garrett</FirstName>

    <LastName>Young</LastName>

  </EmployeeName>

</Employee>

همانطور که دیدید SubQuery به شما کمک می کند خروجی مورد نظر خود را دستکاری کنید و حالت Auto قابلیت بیشتری نسبت به حالت Raw دارد. اما برای کنترل بیشتر می توان از دو حالت PATH و EXPLICIT استفاده کرد.

حالت EXPLICIT Mode

حالت Explicit mode کنترل بسیار کاملی روی XML خروجی در اختیار شما قرار می دهد اما نسبت به دو حالت گفته شده RAW Mode و AUTO Mode پیچیده تر است.

برای استفاده از این حالت بایستی کوئری  SQL طوری نوشته شود که بیانگر کامل سلسله مراتب و ساختار XML مورد نظر شما باشد و هم چنین بایستی برای هر سطح از سلسه مراتب یک دستور Select جداگانه بنویسید و سپس آنها را باهم UNION کنید تا نتیجه مورد نظر حاصل شود.

قوانین متعددی در مورد نحوه تعریف دستور Select وجود دارد که بایستی برای استفاده از حالت Explicit Mode آنها را رعایت کرد که در اینجا از پرداختن به آنها صرف نظر می شود. برای اطلاعات بیشتر می توانید به اینجامراجعه کنید.

زمانی که دستور Select به طور کامل ساخته شد بایستی دو ستون به این دستور اضافه شود که سلسه مراتب XML را تشریح می کند، ستون اول Tag که مقادیر عددی به عنوان شماره هر سطح به آن تخصیص داده می شود و ستون دومی که بایستی به دستور Select اضافه گردد Parent می باشد.

ستون دوم نیز حاوی مقادیر عددی خواهد بود که نشان دهنده والد در سلسه مراتب تولید شده توسط کوئری می باشد که براساس مقادیر نسبت داده شده توسط ستون Tag مقدار دهی می شود.

در سطح اول فیلد Tag حاوی مقدار 1 و فیلد Parent حاولی مقدار null می باشد. هم چنین Select اول بایستی به کلیه ستون هایی که در خروجی XML ظاهر می شوند ارجاع داده باشد. برای این کار ستون ها بایستی شامل Alias باشند. 

دستور Select زیر نتیجه ای مشابه مثال قبل برمی گرداند البته این بار کوئری مفصل تری نوشته شده است:

SELECT 
   1 AS Tag,NULL AS Parent, 
   [e.BusinessEntityID AS [Employee!1!BusinessEntityID,
   [NULL AS [ContactInfo!2!FirstName!ELEMENT,
   [NULL AS [ContactInfo!2!MiddleName!ELEMENT,
   [NULL AS [ContactInfo!2!LastName!ELEMENT,
FROM HumanResources.Employee e INNER JOIN Person.Person c
   ON c.BusinessEntityID = e.BusinessEntityID
WHERE c.FirstName = 'Rob'
UNION ALL
SELECT 2 AS Tag,
   1 AS Parent,
   e.BusinessEntityID,
   c.FirstName,
   c.MiddleName,
   c.LastName
FROM HumanResources.Employee e INNER JOIN Person.Person c
   ON e.BusinessEntityID = c.BusinessEntityID
WHERE c.FirstName = 'Rob'
[ORDER BY [Employee!1!BusinessEntityID], [ContactInfo!2!FirstName!ELEMENT
FOR XML EXPLICIT;

ساختار استفاده شده برای تعریف Alias بدین صورت است :

<ElementName>!<TagNumber>!<AttributeName>[!<OptionalDirective>]

همانگونه که می بینید سه بخش اول الزامی است و بخش آخر دلخواه است:

1. <ElementName>  نام عنصر (Element) که مقادیر بایستی به آن نسبت داده شوند

2. <TagNumber> شماره Tag مربوط به این سطح که مقدار به آن نسبت داده می شود

3. <AttributeName> نام خصیصه Attribute مربوط به ستون مورد نظر در جدول مگر اینکه بخش چهارم مشخص شده باشد، مثلا اگر بخش چهارم Element Directive را مشخص کرده باشد بخش سوم بایستی نام عنصر فرزند را مشخص کند

4. <OptionalDirective> اطلاعات بیشتر برای چگونگی ساخت XML

برای مثال براساس Alias name تعریف شده برای BusinessEntityID می توان دید که خصیصه BusinessEntityID به عنصر <Employee> در سطح اول سلسه مراتب نسبت داده شده است

چون سه ستون بعدی در کوئری به سطح دوم سلسه مراتب نسبت داده شده است که در select دوم تعریف شده است مقدار null به alias name های آنها تخصیص داده شده است که ساختار را ملزم به join دو select تعریف شده در کوئری می کند.

دستور select  دوم ساده تر است اما هم چنان شامل دو ستون Tag, Parent می باشد، ستون های باقی مانده نیز به شکل نرمال تعریف ستون در کوئری تعریف شده اند.

نتیجه کوئری نیز ابتدا بر اساس BusinessEntityID و سپس FirstName مرتب شده اند و در پایان هر دستور Select نیز For XML افزوده شده تا نتیجه هر بخش به صورت XML تولید شود

حاصل اجرای کوئری شرح داده شده در زیر نمایش داده شده است:

<"Employee BusinessEntityID="4>

  <ContactInfo>

    <FirstName>Rob</FirstName>

    <LastName>Walters</LastName>

  </ContactInfo>

</Employee>

<"Employee BusinessEntityID="130>

  <ContactInfo>

    <FirstName>Rob</FirstName>

    <MiddleName>T</MiddleName>

    <LastName>Caron</LastName>

  </ContactInfo>

</Employee>

 ستون BusinessEntityID به شکل یک خصیصه به عنصر <Employee> افزوده شده اگرچه می توان این رفتار را با افزودن Element Directive به صورت یک عنصر فرزند دراین ساختار نمایش داد.

هم چنین می توان برای نمایش ستون هایی که مقدار null دارند ELEMENTS Directive را به ELEMENTSXSINIL تغییر داد.

همانگونه که در مثال دیدید حالت EXPLICIT Mode می تواند سبب پیچیده تر شدن کوئری Select شود مخصوصا اگر می خواهید سطوح بیشتری به خروجی XML بیفزایید. 

خوشبختانه اکثر تنظیماتی که می توان با حالت Explicit انجام داد را می توان با حالت PATH نیزانجام داد اما به روشی ساده تر.

حالت PATH Mode

وقتی که حالت Path را در دستور For XML مشخص می کنید با نام ستون ها یا Alias مربوط به آنها به شکل یک عبارت XPath Expression رفتار می شودکه نشان می دهد مقادیر مربوط به دیتا چگونه بایستی به ساختار سلسه مراتبی XML حاصل تناظر پیدا کنند.

به صورت پیش فرض عناصر XML بر اساس نام ستون ها تعریف می شوند اما می توان این رفتار پیش فرض را با استفاده از سمبل (@) برای تعیین خصیصه ها و سمبل (/) برای تعریف سطوح تغییر داد.

دراینجا یک مثال از رفتار پیش فرض حالت PATH Mode نمایش داده می شود:

SELECT e.BusinessEntityID, c.FirstName,   c.MiddleName, c.LastName
FROM HumanResources.Employee AS e
   INNER JOIN Person.Person AS c
   ON c.BusinessEntityID = e.BusinessEntityID
WHERE c.FirstName = 'Rob'
;FOR XML PATH 

چون در این کوئری هیچ گونه خصیصه و سلسه مراتبی تغییر پیدا نکرده هر ستون به عنوان یک فرزند برای عنصر <row> نمایش داده خواهد شد

<row>

  <BusinessEntityID>4</BusinessEntityID>

  <FirstName>Rob</FirstName>

  <LastName>Walters</LastName>

</row>

<row>

  <BusinessEntityID>130</BusinessEntityID>

  <FirstName>Rob</FirstName>

  <MiddleName>T</MiddleName>

  <LastName>Caron</LastName>

</row>

هم چنین در این روش می توان مشابه حالت های اول عنصر ریشه را اضافه کنید و هم چنین عنصر پیش فرض <row>  را نیز تغییر نام دهید.

فرض کنید می خواهید مقدار BusinessEntityID را به عنوان یک خصیصه به عنصر row که به Employee تغییر نام داده اید اضافه کنید، این کار را به راحتی با افزودن یک Alias به ستون BusinessEntityID که با سمبل @ آغاز می شود انجام دهید:

SELECT e.BusinessEntityID AS "@EmpID",   c.FirstName, c.MiddleName, c.LastName
FROM HumanResources.Employee AS e
   INNER JOIN Person.Person AS c
   ON c.BusinessEntityID = e.BusinessEntityID
WHERE c.FirstName = 'Rob'
;('FOR XML PATH ('Employee'), ROOT ('Employees

حاصل به فرم زیر تغییر می کند:

<Employees>

  <"Employee EmpID="4>

    <FirstName>Rob</FirstName>

    <LastName>Walters</LastName>

  </Employee>

  <"Employee EmpID="168>

    <FirstName>Rob</FirstName>

    <MiddleName>T</MiddleName>

    <LastName>Caron</LastName>

  </Employee>

</Employees>

تنظیمات متداول دیگر نیز که در حالت های قبلی برای نمایش مقادیر null ذکر شد در این حالت نیز قابل انجام است.

برای مثال های بیشتر می توانید به اینجا مراجعه کنید.