Friday, March 23, 2012

OPEN XML QUESTION

DECLARE @.MetaTagXML XML
declare @.XmlDocumentHandlerINT
DECLARE@.QuestionName TABLE(
IdINT IDENTITY(1,1),
[metatag][Varchar](100))
SELECT @.MetaTagXML ='<Report ReportGUID
="CBFF8200-3B52-4F24-9D48-AC5458720B41">
<MetaTags>
<MetaTag>Awareness</MetaTag>
<MetaTag>advertising</MetaTag>
</MetaTags>
<Variables>
<Variable NodeID="7700">
<MetaTags>
<MetaTag>Evaluation</MetaTag>
<MetaTag>account management/primary source experience</MetaTag>
</MetaTags>
</Variable>
<Variable NodeID="7701">
<MetaTags>
<MetaTag>quality</MetaTag>
<MetaTag>quality of account management</MetaTag>
</MetaTags>
</Variable>
</Variables>
</Report>'
EXECUTE SP_XML_PREPAREDOCUMENT @.XmlDocumentHandler OUTPUT, @.MetaTagXML
SELECT*
FROM OPENXML (@.XmlDocumentHandler,
'/Report/Variables/Variable/MetaTags',2)
WITH (
MetaTag varchar(100),
NodeID varchar(100) '../@.NodeID'
)
EXECUTE SP_XML_REMOVEDOCUMENT @.XmlDocumentHandler
i am getting the resultset like this:
Evaluation7700
quality7701
I expect like this
Evaluation7700 CBFF8200-3B52-4F24-9D48-AC5458720B41
account management/primary source experience 7700
CBFF8200-3B52-4F24-9D48-AC5458720B41
quality7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
quality of account management 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
1. how to get all records
2. how to reference reportguid
Thanks in advance
Deva
You want one row per MetaTag element, so your row pattern in OpenXML needs
to select that element.
And you need to add a row for the ReportGUID:
SELECT *
FROM OPENXML (@.XmlDocumentHandler,
'/Report/Variables/Variable/MetaTags/MetaTag',2)
WITH (
MetaTag varchar(100) '.',
NodeID varchar(100) '../../@.NodeID',
ReportID varchar(100) '../../../../@.ReportGUID'
)
Also note that since you are using OpenXML, using the XML datatype is not
necessarily beneficial, since sp_xml_preparedocument will serialize the XML
and reparse it. So using nvarchar(max) may be the better approach to pass
the XML data to the parser.
Best regards
Michael
"xmldev" <xmldev@.discussions.microsoft.com> wrote in message
news:8BD7E081-246C-47A8-8A53-C6FBDD157786@.microsoft.com...
> DECLARE @.MetaTagXML XML
> declare @.XmlDocumentHandler INT
> DECLARE @.QuestionName TABLE (
> Id INT IDENTITY(1,1),
> [metatag] [Varchar](100))
>
> SELECT @.MetaTagXML ='<Report ReportGUID
> ="CBFF8200-3B52-4F24-9D48-AC5458720B41">
> <MetaTags>
> <MetaTag>Awareness</MetaTag>
> <MetaTag>advertising</MetaTag>
> </MetaTags>
> <Variables>
> <Variable NodeID="7700">
> <MetaTags>
> <MetaTag>Evaluation</MetaTag>
> <MetaTag>account management/primary source experience</MetaTag>
> </MetaTags>
> </Variable>
> <Variable NodeID="7701">
> <MetaTags>
> <MetaTag>quality</MetaTag>
> <MetaTag>quality of account management</MetaTag>
> </MetaTags>
> </Variable>
> </Variables>
> </Report>'
> EXECUTE SP_XML_PREPAREDOCUMENT @.XmlDocumentHandler OUTPUT, @.MetaTagXML
> SELECT *
> FROM OPENXML (@.XmlDocumentHandler,
> '/Report/Variables/Variable/MetaTags',2)
> WITH (
> MetaTag varchar(100),
> NodeID varchar(100) '../@.NodeID'
> )
> EXECUTE SP_XML_REMOVEDOCUMENT @.XmlDocumentHandler
> i am getting the resultset like this:
> Evaluation 7700
> quality 7701
> I expect like this
>
> Evaluation 7700 CBFF8200-3B52-4F24-9D48-AC5458720B41
> account management/primary source experience 7700
> CBFF8200-3B52-4F24-9D48-AC5458720B41
> quality 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
> quality of account management 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
> 1. how to get all records
> 2. how to reference reportguid
> Thanks in advance
> Deva
>

No comments:

Post a Comment