August 14, 2015 at 7:47 pm
I have to generate an XML document that matches the following customer specification:
<VENDOR_TEST_RESULT>
<VENDOR IDNUMBER="WAYCOOL1">
<PRODUCTINFO PRODUCTID="1">
<Values...></Values...>
<MoreValues></MoreValues>
</PRODUCTINFO>
<PRODUCTINFO PRODUCTID="2">
<Values...></Values...>
<MoreValues></MoreValues>
</PRODUCTINFO>
...
<PRODUCTINFO PRODUCTID="n">
<Values...></Values...>
<MoreValues></MoreValues>
</PRODUCTINFO>
</VENDOR>
</VENDOR_TEST_RESULT>
Notice that there are what appears to be two root nodes, <VENDOR_TEST_RESULT> and <VENDOR>. Only one of each are allowed per document. This is our customer's specification, so I have no control over it.
First I tried:
FOR XML PATH ('VENDOR') , ROOT('VENDOR_TEST_RESULT'), TYPE
...but that put the <VENDOR> tag on every row of output. Only one allowed per document.
Then I tried to 'trick' the query by specifying the following phrase:
FOR XML PATH ('PRODUCTINFO') , ROOT('VENDOR_TEST_RESULT><VENDOR ID="WAYCOOL1"'), TYPE
...which yielded the following error:
Msg 6850, Level 16, State 1, Line 2
ROOT name 'VENDOR_TEST_RESULT><VENDOR ID="WAYCOOL1"' contains an invalid XML identifier as required by FOR XML; '>'(0x003E) is the first character at fault.
Is there any way to use FOR XML PATH and have these two root nodes, one per document for both (I'd really like to avoid having to try a FOR XML EXPLICIT if at all possible)?
Thank you!
August 15, 2015 at 2:55 am
mtlsql (8/14/2015)
I have to generate an XML document that matches the following customer specification.... This is our customer's specification, so I have no control over it.
Do you have a schema (XSD) for this structure?
😎
First I tried:
FOR XML PATH ('VENDOR') , ROOT('VENDOR_TEST_RESULT'), TYPE
...but that put the <VENDOR> tag on every row of output. Only one allowed per document.
Then I tried to 'trick' the query by specifying the following phrase:
FOR XML PATH ('PRODUCTINFO') , ROOT('VENDOR_TEST_RESULT><VENDOR ID="WAYCOOL1"'), TYPE
...which yielded the following error:
Msg 6850, Level 16, State 1, Line 2
ROOT name 'VENDOR_TEST_RESULT><VENDOR ID="WAYCOOL1"' contains an invalid XML identifier as required by FOR XML; '>'(0x003E) is the first character at fault.
Is there any way to use FOR XML PATH and have these two root nodes, one per document for both (I'd really like to avoid having to try a FOR XML EXPLICIT if at all possible)?
Thank you!
This falls way short of telling us enough to help you, consider the code below as a template for how your question should look like.
USE tempdb;
GO
SET NOCOUNT ON;
GO
/*
Sample data
[VENDOR]--+<[PRODUCT]--+<[PROD_VALUES]
XML Structure
<VENDOR_TEST_RESULT>
<VENDOR IDNUMBER="1">
<PRODUCTINFO PRODUCTID="1">
<VALUE>AB123</VALUE>
</PRODUCTINFO>
</PRODUCTINFO>
</VENDOR>
</VENDOR_TEST_RESULT>
*/
IF OBJECT_ID(N'dbo.PROD_VALUES' ) IS NOT NULL DROP TABLE dbo.PROD_VALUES;
IF OBJECT_ID(N'dbo.PRODUCT' ) IS NOT NULL DROP TABLE dbo.PRODUCT;
IF OBJECT_ID(N'dbo.VENDOR' ) IS NOT NULL DROP TABLE dbo.VENDOR;
CREATE TABLE dbo.VENDOR
(
VENDOR_ID INT NOT NULL CONSTRAINT PK_VENDOR PRIMARY KEY CLUSTERED
,VENDOR_NAME VARCHAR(20) NOT NULL
);
CREATE TABLE dbo.PRODUCT
(
PRODUCT_ID INT NOT NULL CONSTRAINT PK_PRODUCT PRIMARY KEY CLUSTERED
,VENDOR_ID INT NOT NULL CONSTRAINT FK_PRODUCT_VENDOR FOREIGN KEY REFERENCES dbo.VENDOR(VENDOR_ID)
,PRODUCT_NAME VARCHAR(20) NOT NULL
);
CREATE TABLE dbo.PROD_VALUES
(
PROD_VALUES_ID INT NOT NULL CONSTRAINT PK_PROD_VALUES PRIMARY KEY CLUSTERED
,PRODUCT_ID INT NOT NULL CONSTRAINT FK_PROD_VALUES_PRODUCT FOREIGN KEY REFERENCES dbo.PRODUCT(PRODUCT_ID)
,PROD_VALUES_VALUE VARCHAR(20) NOT NULL
);
INSERT INTO dbo.VENDOR (VENDOR_ID,VENDOR_NAME)
VALUES (1,'VENDOR_1')
,(2,'VENDOR_2')
,(3,'VENDOR_3')
;
INSERT INTO dbo.PRODUCT (PRODUCT_ID,VENDOR_ID,PRODUCT_NAME)
VALUES (1,1,'V1P1')
,(2,1,'V1P2')
,(3,2,'V2P1')
,(4,2,'V2P2')
,(5,3,'V3P1')
,(6,3,'V3P2')
;
INSERT INTO dbo.PROD_VALUES (PROD_VALUES_ID,PRODUCT_ID,PROD_VALUES_VALUE)
VALUES ( 1,1,'AB123')
,( 2,1,'BB123')
,( 3,2,'AC123')
,( 4,2,'BC123')
,( 5,3,'AD123')
,( 6,3,'BD123')
,( 7,4,'AE123')
,( 8,4,'BE123')
,( 9,5,'AF123')
,(10,5,'BF123')
,(11,6,'AG123')
,(12,6,'BG123')
;
/* Attempts so far */
SELECT
V.VENDOR_ID AS '@IDNUMBER'
,(
SELECT
P.PRODUCT_ID AS '@PRODUCTID'
,(
SELECT
PV.PROD_VALUES_VALUE AS 'VALUE'
FROM dbo.PROD_VALUES PV
WHERE PV.PRODUCT_ID = P.PRODUCT_ID
FOR XML PATH(''),TYPE
)
FROM dbo.PRODUCT P
WHERE P.VENDOR_ID = V.VENDOR_ID
FOR XML PATH('PRODUCTINFO'),TYPE
)
FROM dbo.VENDOR V
FOR XML PATH('VENDOR'), ROOT('VENDOR_TEST_RESULT');
Output
<VENDOR_TEST_RESULT>
<VENDOR IDNUMBER="1">
<PRODUCTINFO PRODUCTID="1">
<VALUE>AB123</VALUE>
<VALUE>BB123</VALUE>
</PRODUCTINFO>
<PRODUCTINFO PRODUCTID="2">
<VALUE>AC123</VALUE>
<VALUE>BC123</VALUE>
</PRODUCTINFO>
</VENDOR>
<VENDOR IDNUMBER="2">
<PRODUCTINFO PRODUCTID="3">
<VALUE>AD123</VALUE>
<VALUE>BD123</VALUE>
</PRODUCTINFO>
<PRODUCTINFO PRODUCTID="4">
<VALUE>AE123</VALUE>
<VALUE>BE123</VALUE>
</PRODUCTINFO>
</VENDOR>
<VENDOR IDNUMBER="3">
<PRODUCTINFO PRODUCTID="5">
<VALUE>AF123</VALUE>
<VALUE>BF123</VALUE>
</PRODUCTINFO>
<PRODUCTINFO PRODUCTID="6">
<VALUE>AG123</VALUE>
<VALUE>BG123</VALUE>
</PRODUCTINFO>
</VENDOR>
</VENDOR_TEST_RESULT>
August 22, 2015 at 12:48 am
Sorry it took me so long to respond, I was temporarily assigned to other projects, but am now back to "XML Hell".
I'd like to thank Eirikur for putting together a more thorough example; the actual project I'm applying this concept to pulls data from several disparate SQL Server and Oracle data sources, and combines 5 tables that are >130 columns wide. I tried to simplify for this post, apparently I overdid it...
Anyway, the example here still doesn't get me where I need to be. Notice that there are multiple vendor tags. In this case, WE are the vendor. The XML is being sent to our customer. The VendorID is assigned to us by them, and can appear only once in the XML document.
The problem is that they have already specified a second "root" tag, <VENDOR_TEST_RESULT>. So the resulting XML has to look like:
<VENDOR_TEST_RESULT>
<VENDOR IDNUMBER="WayCoolVendor">
<PRODUCTINFO PRODUCTID="1">
<VALUE>AB123</VALUE>
<VALUE>BB123</VALUE>
</PRODUCTINFO>
<PRODUCTINFO PRODUCTID="2">
<VALUE>AC123</VALUE>
<VALUE>BC123</VALUE>
</PRODUCTINFO>
<PRODUCTINFO PRODUCTID="3">
<VALUE>AD123</VALUE>
<VALUE>BD123</VALUE>
</PRODUCTINFO>
<PRODUCTINFO PRODUCTID="4">
<VALUE>AE123</VALUE>
<VALUE>BE123</VALUE>
</PRODUCTINFO>
<PRODUCTINFO PRODUCTID="5">
<VALUE>AF123</VALUE>
<VALUE>BF123</VALUE>
</PRODUCTINFO>
<PRODUCTINFO PRODUCTID="6">
<VALUE>AG123</VALUE>
<VALUE>BG123</VALUE>
</PRODUCTINFO>
</VENDOR>
</VENDOR_TEST_RESULT>
Notice that there is only 1 VENDOR_TEST_RESULT tag pair, 1 VENDOR tag pair nested inside of it, and then all of the repeating data nodes. It's like there's two root nodes.
And no, we weren't given an XSD or DTD specification. I believe it's built into the encryption tool they provided us, since it returns a bunch of "tag not declared" errors.
Thanks again for any suggestions!
August 22, 2015 at 1:21 am
To get the desired result set, we only need to do minor changes to the query, note that there is one "FOR XM" for each nesting level in the output.
😎
SELECT
12345 AS '@IDNUMBER'
,(
SELECT
P.PRODUCT_ID AS '@PRODUCTID'
,(
SELECT
PV.PROD_VALUES_VALUE AS 'VALUE'
FROM dbo.PROD_VALUES PV
WHERE PV.PRODUCT_ID = P.PRODUCT_ID
FOR XML PATH(''),TYPE
)
FROM dbo.PRODUCT P
FOR XML PATH('PRODUCTINFO'),TYPE
)
FOR XML PATH('VENDOR'),ROOT('VENDOR_TEST_RESULT');
Results
<VENDOR_TEST_RESULT>
<VENDOR IDNUMBER="12345">
<PRODUCTINFO PRODUCTID="1">
<VALUE>AB123</VALUE>
<VALUE>BB123</VALUE>
</PRODUCTINFO>
<PRODUCTINFO PRODUCTID="2">
<VALUE>AC123</VALUE>
<VALUE>BC123</VALUE>
</PRODUCTINFO>
<PRODUCTINFO PRODUCTID="3">
<VALUE>AD123</VALUE>
<VALUE>BD123</VALUE>
</PRODUCTINFO>
<PRODUCTINFO PRODUCTID="4">
<VALUE>AE123</VALUE>
<VALUE>BE123</VALUE>
</PRODUCTINFO>
<PRODUCTINFO PRODUCTID="5">
<VALUE>AF123</VALUE>
<VALUE>BF123</VALUE>
</PRODUCTINFO>
<PRODUCTINFO PRODUCTID="6">
<VALUE>AG123</VALUE>
<VALUE>BG123</VALUE>
</PRODUCTINFO>
</VENDOR>
</VENDOR_TEST_RESULT>
August 22, 2015 at 9:45 am
Wow, Eirikur, this worked great! I think what was throwing me was revealed here in the outer SQL statement:
SELECT
12345 AS '@IDNUMBER'
,(
-- nested SQL statement...
)
FOR XML PATH('VENDOR'),ROOT('VENDOR_TEST_RESULT');
In all the other examples I've ever looked at, I've never seen a query that had a FOR XML PATH clause and no FROM clause. Just SELECT and FOR XML PATH.
Thanks again, this was really helpful! 😀
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply