xml / sql help

Tuesday, August 30, 2011 3:14 PM

0

I've been tasked with writing SQL that returns the following XML:

<InvoiceDeliveryResponse>

<Invoice InvoiceNumber="asfasfasfasf" VendorNumber="dasdgasdasd">

    <WorkOrders>

      <WorkOrder WorkOrderNumber="aaaaa">

        <LineItems>

          <LineItem LineItemNumber="1" Status="Fail" ErrorMsg="Error message will be here" />

          <LineItem LineItemNumber="2" Status="Pass" />

          <LineItem LineItemNumber="3" Status="Pass" />

        </LineItems>

      </WorkOrder>

      <WorkOrder WorkOrderNumber="bbbb">

        <LineItems>

          <LineItem LineItemNumber="1" Status="Fail" ErrorMsg="Error message will be here" />

          <LineItem LineItemNumber="2" Status="Pass" />

        </LineItems>

      </WorkOrder>

    </WorkOrders>

</Invoice>

</InvoiceDeliveryResponse>

I've tried several variations of EXPLICIT and PATH, but can't quite get what I want as output. The closest I've gotten is using EXPLICIT and I get the following (more or less correct, but not grouped the way I'd like):

<InvoiceDeliveryResponse>
<Invoice InvoiceNumber="ABC123" VendorNumber="1" />
<Invoice InvoiceNumber="ASDAGFGA1" VendorNumber="44" />
<Invoice InvoiceNumber="dfsdff" VendorNumber="1" />
<Invoice InvoiceNumber="dfsdff" VendorNumber="22" />
<Invoice InvoiceNumber="GGFFF333" VendorNumber="66" />
<Invoice InvoiceNumber="ZY53224" VendorNumber="33">
    <WorkOrders>
      <WorkOrder WorkOrderNumber="123" />
      <WorkOrder WorkOrderNumber="22222" />
      <WorkOrder WorkOrderNumber="3333" />
      <WorkOrder WorkOrderNumber="5555" />
      <WorkOrder WorkOrderNumber="74534">
        <LineItems>
          <LineItem LineItemNumber="1" Status="0" ErrorMsg=" The field SI56SICIP is a required field. The field SI56SIPROJ is a required field." />
          <LineItem LineItemNumber="1" Status="0" ErrorMsg=" This record is part of a batch with a line item that did not pass validation." />
          <LineItem LineItemNumber="1" Status="1" ErrorMsg="" />
          <LineItem LineItemNumber="2" Status="0" ErrorMsg=" The field SIRMK is larger than that allowed on the SQL Server. Maximum length allowed is 30; length found was 107." />
          <LineItem LineItemNumber="2" Status="1" ErrorMsg="" />
        </LineItems>
      </WorkOrder>
    </WorkOrders>
</Invoice>
</InvoiceDeliveryResponse>

Thanks in advance!

(here's the SQL I used to get the result above):

SELECT
   1 AS Tag,
   NULL AS Parent,
   SIVINV        AS [Invoice!1!InvoiceNumber],
   SIAN8         AS [Invoice!1!VendorNumber],
   NULL          AS [WorkOrders!2!],
   NULL          AS [WorkOrder!3!WorkOrderNumber],
   NULL          AS [LineItems!4!],
   NULL          AS [LineItem!5!LineItemNumber],
   NULL          AS [LineItem!5!Status],
   NULL          AS [LineItem!5!ErrorMsg]
FROM
   dbo.F56SIAPU_tmp_01 (NOLOCK)

UNION

SELECT
   2 AS Tag,
   1 AS Parent,
   NULL,
   NULL,
   '',
   NULL,
   NULL,
   NULL,
   NULL,
   NULL
FROM
   dbo.F56SIAPU_tmp_01 (NOLOCK)

UNION

SELECT
   3 AS Tag,
   2 AS Parent,
   NULL,
   NULL,
   NULL,
   SIWOD,
   NULL,
   NULL,
   NULL,
   NULL
FROM
   dbo.F56SIAPU_tmp_01 (NOLOCK)

UNION

SELECT
   4 AS Tag,
   3 AS Parent,
   NULL,
   NULL,
   NULL,
   NULL,
   '',
   NULL,
   NULL,
   NULL

UNION

SELECT
   5 AS Tag,
   4 AS Parent,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   SILNID,
   IsProcessed,
   ErrorMessage
FROM
   dbo.F56SIAPU_tmp_01 (NOLOCK)
ORDER BY
   Tag,
   Parent,
   [Invoice!1!InvoiceNumber],
   [Invoice!1!VendorNumber],
   [WorkOrders!2!],
   [WorkOrder!3!WorkOrderNumber],
   [LineItems!4!],
   [LineItem!5!LineItemNumber],
   [LineItem!5!Status],
   [LineItem!5!ErrorMsg]

FOR XML EXPLICIT, ROOT('InvoiceDeliveryResponse')
- Reply
- Quote

All Replies

Tuesday, August 30, 2011 3:22 PM

0

Post a concise and complete script. We don't have a table named F56SIAPU_tmp_01.

btw, not every desired output is possible to generate with T-SQL only. Consider using a XSLT stylesheet to transform your XML output.
- Reply
- Quote

Tuesday, August 30, 2011 3:49 PM

Answerer

What version of SQL Server are you using. Stefan is quite right about posting up sample data and scripts, however looking at your data, if it was stored in a normal relational format then this would be pretty straightforward using FOR XML PATH ( SQL 2005 onwards ). Demo below:

DECLARE @invoices TABLE ( invoiceId INT PRIMARY KEY, InvoiceNumber VARCHAR(50), VendorNumber VARCHAR(50) )
DECLARE @workOrders TABLE ( workOrderId INT PRIMARY KEY, invoiceId INT NOT NULL, WorkOrderNumber VARCHAR(50) )
DECLARE @lineItems TABLE ( lineItemId INT PRIMARY KEY, workOrderId INT NOT NULL, LineItemNumber INT, lineItemStatus VARCHAR(20), errorMsg VARCHAR(100) )

INSERT INTO @invoices ( invoiceId, InvoiceNumber, VendorNumber )
VALUES ( 1, 'asfasfasfasf', 'dasdgasdasd' )

INSERT INTO @workOrders ( workOrderId, invoiceId, WorkOrderNumber )
VALUES ( 100, 1, 'aaaaa' )

INSERT INTO @workOrders ( workOrderId, invoiceId, WorkOrderNumber )
VALUES ( 101, 1, 'bbbb' )

INSERT INTO @lineItems ( lineItemId, workOrderId, LineItemNumber, lineItemStatus, errorMsg )
VALUES ( 2000, 100, 1, 'Fail', 'Error message will be here' )

INSERT INTO @lineItems ( lineItemId, workOrderId, LineItemNumber, lineItemStatus, errorMsg )
VALUES ( 2001, 100, 2, 'Pass', NULL )

INSERT INTO @lineItems ( lineItemId, workOrderId, LineItemNumber, lineItemStatus, errorMsg )
VALUES ( 2002, 100, 3, 'Pass', NULL )

INSERT INTO @lineItems ( lineItemId, workOrderId, LineItemNumber, lineItemStatus, errorMsg )
VALUES ( 2003, 101, 1, 'Fail', 'Error message will be here' )

INSERT INTO @lineItems ( lineItemId, workOrderId, LineItemNumber, lineItemStatus, errorMsg )
VALUES ( 2004, 101, 2, 'Pass', NULL )


SELECT 
	InvoiceNumber AS "@InvoiceNumber",
	VendorNumber AS "@VendorNumber",
	(
	SELECT
		WorkOrderNumber AS "@WorkOrderNumber",
		(
		SELECT
			lineItemNumber AS "@LineItemNumber",
			lineItemStatus AS "@Status",
			errorMsg AS "@ErrorMsg"
		FROM @lineItems li
		WHERE wo.workOrderId = li.workOrderId
		FOR XML PATH('LineItem'), TYPE
		) AS "LineItems"
	FROM @workOrders wo
	WHERE i.invoiceId = wo.invoiceId
	FOR XML PATH('WorkOrder'), TYPE
	) AS "WorkOrders"
FROM @invoices i
FOR XML PATH('Invoice'), ROOT('InvoiceDeliveryResponse' )

Tuesday, August 30, 2011 5:02 PM

0

Thanks to both of you.

I was able to get Explicit to work after (thoroughly) reviewing the following article:

http://msdn.microsoft.com/en-us/library/bb522512(v=SQL.100).aspx

SELECT
   1 AS Tag,
   0 AS Parent,
   SIVINV        AS [Invoice!1!InvoiceNumber],
   SIAN8         AS [Invoice!1!VendorNumber],
   NULL          AS [WorkOrders!2!],
   NULL          AS [WorkOrder!3!WorkOrderNumber],
   NULL          AS [LineItems!4!],
   NULL          AS [LineItem!5!LineItemNumber],
   NULL          AS [LineItem!5!Status],
   NULL          AS [LineItem!5!ErrorMsg]
FROM
   dbo.F56SIAPU_tmp_01 (NOLOCK)

UNION

SELECT
   2 AS Tag,
   1 AS Parent,
   SIVINV,
   NULL,
   '',
   SIWOD,
   NULL,
   NULL,
   NULL,
   NULL
FROM
   dbo.F56SIAPU_tmp_01 (NOLOCK)

UNION

SELECT
   3 AS Tag,
   2 AS Parent,
   SIVINV,
   NULL,
   NULL,
   SIWOD,
   NULL,
   NULL,
   NULL,
   NULL
FROM
   dbo.F56SIAPU_tmp_01 (NOLOCK)

UNION

SELECT
   4 AS Tag,
   3 AS Parent,
   SIVINV,
   NULL,
   NULL,
   SIWOD,
   '',
   NULL,
   NULL,
   NULL
FROM
   dbo.F56SIAPU_tmp_01 (NOLOCK)

UNION

SELECT
   5 AS Tag,
   4 AS Parent,
   SIVINV,
   NULL,
   NULL,
   SIWOD,
   NULL,
   SILNID,
   CASE
      WHEN IsProcessed = 0 THEN 'Fail'
      WHEN IsProcessed = 1 THEN 'Pass'
   END,
   LTRIM(ErrorMessage)
FROM
   dbo.F56SIAPU_tmp_01 (NOLOCK)
ORDER BY
   [Invoice!1!InvoiceNumber],
   [WorkOrder!3!WorkOrderNumber]
FOR XML EXPLICIT, ROOT('InvoiceDeliveryResponse')
- Reply
- Quote