如何使用以下格式的T-SQL创建XML文件 [英] How to create XML file using T-SQL in below format
问题描述
我正在使用此查询，
SELECT HEAD.ID ASHEAD / @ ID，
HEAD.CODE AS HEAD / CODE，
CONVERT（VARCHAR，HEAD.DATE，112）ASHEAD / DATE，
HEAD.DOCNO作为HEAD / DOCNO，
ITEM.SERIAL作为HEAD / ITEM / @ ID，
ITEM.ITEMNO作为HEAD / ITEM / ITEMNO，
ITEM.UOM作为HEAD / ITEM / UOM，
ITEM.QTY作为HEAD / ITEM / QTY
来自PRODUCT_HDR HEAD，PRODUCT_DTL项目
在哪里HEAD.CODE = ITEM.CODE和HEAD.DATE = ITEM.DATE
FOR XML PATH（'IDOC'），ELEMENTS;
得到以下格式的结果，但它不起作用。
<？ xml version = 1.0 编码 = utf-8 ？ >
< HEAD ID = 1 >
< 代码 > 001 < / CODE >
< DATE > 20141208 < / DATE >
< DOCNO > ; 2001500000001 < / DOCNO >
< ITEM ID = 1 >
< ITEMNO > 10001 < / ITEMNO >
< UOM > KG < / UOM >
< QTY > 10.000 < / QTY >
< / ITEM >
< ITEM ID = 2 >
< ITEMNO > 10002 < / ITEMNO >
< UOM > KG < / UOM >
< QTY > 20.000 < / QTY >
< / ITEM >
< ITEM ID = 3 > ;
< ITEMNO > 10003 < / ITEMNO >
< UOM > KG < / UOM >
< QTY > 30.000 < / QTY >
< / ITEM >
< / HEAD >
SQL中的数据按行组织是这样的：
head1 ITEM1 头像1 ITEM2 头像1 项目3 HEAD2 ITEM1 HEAD2 ITEM2
等......
但你的XML反映了一个结构像这样...
head1 ITEM1 ITEM2 项目3 HEAD2 ITEM1 item2
这是SQL无法处理的结构...
我得到了解决方案。它在下面，
SELECT HEAD.ID为HEAD / @ ID，
HEAD.CODE为HEAD / CODE，
CONVERT（VARCHAR，HEAD.DATE，112）ASHEAD / DATE，
HEAD.DOCNO ASHEAD / DOCNO，
（选择ITEM.SERIAL作为@ID，
ITEM.ITEMNO作为ITEMNO，
ITEM.UOM作为UOM，
ITEM.QTY为QTY
来自PRODUCT_DTL项目
WHERE HEAD.CODE = ITEM.CODE和HEAD.DATE = ITEM.DATE
FOR XML PATH（'ITEM'），TYPE，ELEMENTS）ASHEAD
FROM PRODUCT_HDR HEAD
for XML PATH（'IDOC'），ELEMENTS;
I am using this query,
SELECT HEAD.ID AS "HEAD/@ID",
HEAD.CODE AS "HEAD/CODE",
CONVERT(VARCHAR,HEAD.DATE,112) AS "HEAD/DATE",
HEAD.DOCNO AS "HEAD/DOCNO",
ITEM.SERIAL AS "HEAD/ITEM/@ID",
ITEM.ITEMNO AS "HEAD/ITEM/ITEMNO",
ITEM.UOM AS "HEAD/ITEM/UOM",
ITEM.QTY AS "HEAD/ITEM/QTY"
FROM PRODUCT_HDR HEAD, PRODUCT_DTL ITEM
WHERE HEAD.CODE = ITEM.CODE AND HEAD.DATE = ITEM.DATE
FOR XML PATH ('IDOC'), ELEMENTS;
to get result in below format, but its not working.
<?xml version="1.0" encoding="utf-8" ?>
<HEAD ID="1">
<CODE>001</CODE>
<DATE>20141208</DATE>
<DOCNO>2001500000001</DOCNO>
<ITEM ID="1">
<ITEMNO>10001</ITEMNO>
<UOM>KG</UOM>
<QTY>10.000</QTY>
</ITEM>
<ITEM ID="2">
<ITEMNO>10002</ITEMNO>
<UOM>KG</UOM>
<QTY>20.000</QTY>
</ITEM>
<ITEM ID="3">
<ITEMNO>10003</ITEMNO>
<UOM>KG</UOM>
<QTY>30.000</QTY>
</ITEM>
</HEAD>Your data in SQL organised in rows like this:
head1 item1 head1 item2 head1 item3 head2 item1 head2 item2
and so on...
But your XML reflects a structure like this...
head1 item1 item2 item3 head2 item1 item2
This is a structure SQL can not handle...
I got the solution. Its right below,
SELECT HEAD.ID AS "HEAD/@ID",
HEAD.CODE AS "HEAD/CODE",
CONVERT(VARCHAR,HEAD.DATE,112) AS "HEAD/DATE",
HEAD.DOCNO AS "HEAD/DOCNO",
(SELECT ITEM.SERIAL AS "@ID",
ITEM.ITEMNO AS "ITEMNO",
ITEM.UOM AS "UOM",
ITEM.QTY AS "QTY"
FROM PRODUCT_DTL ITEM
WHERE HEAD.CODE = ITEM.CODE AND HEAD.DATE = ITEM.DATE
FOR XML PATH ('ITEM'), TYPE, ELEMENTS) AS "HEAD"
FROM PRODUCT_HDR HEAD
FOR XML PATH ('IDOC'), ELEMENTS;
这篇关于如何使用以下格式的T-SQL创建XML文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
