Link to home
Start Free TrialLog in
Avatar of Alyanto
AlyantoFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Loading generated XML to a table

I have the query below which produces the XML I need.  The problem is I want to insert it into a table or a variable.  How is this best done from SQL server?

SELECT
            1 AS 'Tag'
            ,NULL As 'Parent'
            ,0 AS 'Branches!1!Sort!hide'
            ,NULL AS 'Branches!1!'
            ,NULL AS 'Branch!2!BranchNumber!ID'
            ,NULL AS 'Branch!2!BranchName'
            ,NULL AS 'Branch!2!BranchDistrict'
            ,NULL AS 'Branch!2!BranchRegion'
            ,NULL AS 'Branch!2!BranchZone'
            ,NULL AS 'Branch!2!BranchCountry'
            ,NULL AS 'Branch!2!BranchCountryNo'
            ,NULL AS 'Despatchs!3!ELEMENT'
            ,NULL AS 'Stock!5!ELEMENT'
            ,NULL AS 'Despatch!4!LongCode!ID'
            ,NULL AS 'Despatch!4!Units'
            ,NULL AS 'Despatch!4!Memo'
            ,NULL AS 'Despatch!4!TransactionType'
            ,NULL AS 'Despatch!4!OtherBranchNumber'
            ,NULL AS 'Item!6!LongCode!ID'
            ,NULL AS 'Item!6!ShortCode'
            ,NULL AS 'Item!6!ProductDescription'
            ,NULL AS 'Item!6!BranchSellingPrice'
            ,NULL AS 'Item!6!CurrentStockUnits'
            ,NULL AS 'Item!6!ToPickUnits'
UNION ALL
      SELECT DISTINCT
            2 AS Tag
            ,1 AS Parent
            ,br.BranchNumber * 100
            ,NULL
            ,br.BranchNumber
            ,br.BranchName
            ,br.BranchDistrict
            ,br.BranchRegion
            ,br.BranchZone
            ,br.BranchCountry
            ,br.BranchCountryNo
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
      FROM  dbo.PI_Dispatch di INNER JOIN [dbo].[PI_BranchStock] br ON di.BranchNumber = br.BranchNumber
      WHERE di.BranchNumber = 15
UNION ALL
SELECT DISTINCT
    3 AS Tag
    ,2 AS Parent
    ,BranchNumber * 100 + 1
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
UNION ALL
SELECT DISTINCT
    4 AS Tag
    ,3 AS Parent
    ,BranchNumber * 100 + 2
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,LongCode
    ,Units
    ,TransactionType
    ,Memo
    ,OtherBranchNumber
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
UNION ALL
      SELECT DISTINCT
            5 AS Tag
            ,2 AS Parent
            ,BranchNumber * 100
            ,NULL
            ,BranchNumber AS Trial
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
      FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
      UNION ALL
      SELECT DISTINCT
            6 AS Tag
            ,5 AS Parent
            ,di.BranchNumber * 100
            ,NULL
            ,di.BranchNumber AS Trial
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,br.LongCode
            ,br.ShortCode
            ,br.ProductDescription
            ,br.BranchSellingPrice
            ,br.CurrentStockUnits
            ,br.ToPickUnits
      FROM  dbo.PI_Dispatch di INNER JOIN [dbo].[PI_BranchStock] br ON di.BranchNumber = br.BranchNumber
      WHERE di.BranchNumber = 15
ORDER BY [Branches!1!Sort!hide]
FOR XML EXPLICIT)
Avatar of Zberteoc
Zberteoc
Flag of Canada image
declare
	@xml_string varchar(max)
SET
	@xml_string=(
SELECT
            1 AS 'Tag'
            ,NULL As 'Parent'
            ,0 AS 'Branches!1!Sort!hide'
            ,NULL AS 'Branches!1!'
            ,NULL AS 'Branch!2!BranchNumber!ID'
            ,NULL AS 'Branch!2!BranchName'
            ,NULL AS 'Branch!2!BranchDistrict'
            ,NULL AS 'Branch!2!BranchRegion'
            ,NULL AS 'Branch!2!BranchZone'
            ,NULL AS 'Branch!2!BranchCountry'
            ,NULL AS 'Branch!2!BranchCountryNo'
            ,NULL AS 'Despatchs!3!ELEMENT'
            ,NULL AS 'Stock!5!ELEMENT'
            ,NULL AS 'Despatch!4!LongCode!ID'
            ,NULL AS 'Despatch!4!Units'
            ,NULL AS 'Despatch!4!Memo'
            ,NULL AS 'Despatch!4!TransactionType'
            ,NULL AS 'Despatch!4!OtherBranchNumber'
            ,NULL AS 'Item!6!LongCode!ID'
            ,NULL AS 'Item!6!ShortCode'
            ,NULL AS 'Item!6!ProductDescription'
            ,NULL AS 'Item!6!BranchSellingPrice'
            ,NULL AS 'Item!6!CurrentStockUnits'
            ,NULL AS 'Item!6!ToPickUnits'
UNION ALL
      SELECT DISTINCT
            2 AS Tag
            ,1 AS Parent
            ,br.BranchNumber * 100
            ,NULL 
            ,br.BranchNumber
            ,br.BranchName 
            ,br.BranchDistrict
            ,br.BranchRegion
            ,br.BranchZone 
            ,br.BranchCountry 
            ,br.BranchCountryNo 
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
      FROM  dbo.PI_Dispatch di INNER JOIN [dbo].[PI_BranchStock] br ON di.BranchNumber = br.BranchNumber 
      WHERE di.BranchNumber = 15
UNION ALL
SELECT DISTINCT
    3 AS Tag
    ,2 AS Parent
    ,BranchNumber * 100 + 1
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
UNION ALL
SELECT DISTINCT
    4 AS Tag
    ,3 AS Parent
    ,BranchNumber * 100 + 2
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,LongCode
    ,Units
    ,TransactionType
    ,Memo
    ,OtherBranchNumber
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
UNION ALL
      SELECT DISTINCT
            5 AS Tag
            ,2 AS Parent
            ,BranchNumber * 100
            ,NULL
            ,BranchNumber AS Trial
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL 
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
      FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
      UNION ALL
      SELECT DISTINCT
            6 AS Tag
            ,5 AS Parent
            ,di.BranchNumber * 100
            ,NULL
            ,di.BranchNumber AS Trial
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL 
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,br.LongCode
            ,br.ShortCode
            ,br.ProductDescription 
            ,br.BranchSellingPrice 
            ,br.CurrentStockUnits
            ,br.ToPickUnits 
      FROM  dbo.PI_Dispatch di INNER JOIN [dbo].[PI_BranchStock] br ON di.BranchNumber = br.BranchNumber 
      WHERE di.BranchNumber = 15
ORDER BY [Branches!1!Sort!hide]
FOR XML EXPLICIT)

SELECT @xml_string

Open in new window

Avatar of Zberteoc
Zberteoc
Flag of Canada image
To insert into an XML variable:
declare
	@xml xml
SET
	@xml=CONVERT(xml, (
SELECT
            1 AS 'Tag'
            ,NULL As 'Parent'
            ,0 AS 'Branches!1!Sort!hide'
            ,NULL AS 'Branches!1!'
            ,NULL AS 'Branch!2!BranchNumber!ID'
            ,NULL AS 'Branch!2!BranchName'
            ,NULL AS 'Branch!2!BranchDistrict'
            ,NULL AS 'Branch!2!BranchRegion'
            ,NULL AS 'Branch!2!BranchZone'
            ,NULL AS 'Branch!2!BranchCountry'
            ,NULL AS 'Branch!2!BranchCountryNo'
            ,NULL AS 'Despatchs!3!ELEMENT'
            ,NULL AS 'Stock!5!ELEMENT'
            ,NULL AS 'Despatch!4!LongCode!ID'
            ,NULL AS 'Despatch!4!Units'
            ,NULL AS 'Despatch!4!Memo'
            ,NULL AS 'Despatch!4!TransactionType'
            ,NULL AS 'Despatch!4!OtherBranchNumber'
            ,NULL AS 'Item!6!LongCode!ID'
            ,NULL AS 'Item!6!ShortCode'
            ,NULL AS 'Item!6!ProductDescription'
            ,NULL AS 'Item!6!BranchSellingPrice'
            ,NULL AS 'Item!6!CurrentStockUnits'
            ,NULL AS 'Item!6!ToPickUnits'
UNION ALL
      SELECT DISTINCT
            2 AS Tag
            ,1 AS Parent
            ,br.BranchNumber * 100
            ,NULL 
            ,br.BranchNumber
            ,br.BranchName 
            ,br.BranchDistrict
            ,br.BranchRegion
            ,br.BranchZone 
            ,br.BranchCountry 
            ,br.BranchCountryNo 
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
      FROM  dbo.PI_Dispatch di INNER JOIN [dbo].[PI_BranchStock] br ON di.BranchNumber = br.BranchNumber 
      WHERE di.BranchNumber = 15
UNION ALL
SELECT DISTINCT
    3 AS Tag
    ,2 AS Parent
    ,BranchNumber * 100 + 1
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
UNION ALL
SELECT DISTINCT
    4 AS Tag
    ,3 AS Parent
    ,BranchNumber * 100 + 2
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,LongCode
    ,Units
    ,TransactionType
    ,Memo
    ,OtherBranchNumber
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
UNION ALL
      SELECT DISTINCT
            5 AS Tag
            ,2 AS Parent
            ,BranchNumber * 100
            ,NULL
            ,BranchNumber AS Trial
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL 
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
      FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
      UNION ALL
      SELECT DISTINCT
            6 AS Tag
            ,5 AS Parent
            ,di.BranchNumber * 100
            ,NULL
            ,di.BranchNumber AS Trial
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL 
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,br.LongCode
            ,br.ShortCode
            ,br.ProductDescription 
            ,br.BranchSellingPrice 
            ,br.CurrentStockUnits
            ,br.ToPickUnits 
      FROM  dbo.PI_Dispatch di INNER JOIN [dbo].[PI_BranchStock] br ON di.BranchNumber = br.BranchNumber 
      WHERE di.BranchNumber = 15
ORDER BY [Branches!1!Sort!hide]
FOR XML EXPLICIT))

SELECT @xml

Open in new window

Avatar of Zberteoc
Zberteoc
Flag of Canada image
From variable you can insert into a table easily. You cant insert directly from query if you use FOR XML.
Avatar of Alyanto
Alyanto
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Option one resulted in this message

Msg 1086, Level 15, State 1, Line 166
The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.


Option Two Resulted in this message
Msg 1086, Level 15, State 1, Line 166
The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.


A little cryptic but I think it means

SELECT * FROM ( SELECT ... FROM ADateSource) AS DAT or similar for each sub query, or am I barking up the wrong tree?
Avatar of Zberteoc
Zberteoc
Flag of Canada image
Ok, instead of set use select:

declare
	@xml_string varchar(max)
SELECT
	@xml_string=
...(

Open in new window

Avatar of Alyanto
Alyanto
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

My choices seem to be put the data in a table and deal with it from there, export it to a file and deal with it from there or send it to a variable.  I would be able to work this through with most of those options.

I tried the encasing the sub queries this did not work!
Avatar of Alyanto
Alyanto
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

I tried the third option proposed and this was the result:

Msg 1086, Level 15, State 1, Line 167
The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.
Avatar of Zberteoc
Zberteoc
Flag of Canada image
Can you post the query you tried here?
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image
The part that you are missing is:
1. FOR XML EXPLICIT, TYPE
2. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Alyanto
Alyanto
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Thank you Anthony for your answer this has salvaged a considerable amount of work to date.  I would also like to acknowledge the contribution of the Zberteoc who was clearly going in the right direction.