XML to inserting into Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

XML to inserting into Table

Author

Topic

kumar1248
Starting Member

20 Posts

Posted - 2009-07-21 : 18:02:12

Hi Team,
I have the data format as below


<hello>
  <xselement name="TransactionSetID" minOccurs="0" LenValue="3" />
  <xselement name="SeqNum" minOccurs="0" LenValue="9" />
  <xselement name="ReportTypeCd" minOccurs="0" LenValue="25" />
  <xselement name="RecordTypeCd" minOccurs="0" LenValue="25" />
  <xselement name="WCTRPNum" minOccurs="0" LenValue="25" />
  <xselement name="WCISScreenName" minOccurs="0" LenValue="25" />
  <xselement name="DNName" minOccurs="0" LenValue="25" />
  <xselement name="ProcessDate" minOccurs="0" LenValue="8" />
  <xselement name="ProcessTime" minOccurs="0" LenValue="8" />
  <xselement name="InsurerFEIN" minOccurs="0" LenValue="10" />
  <xselement name="ClaimAdminPostalCode" minOccurs="0" LenValue="10" />
  <xselement name="ClaimAdminFEIN" minOccurs="0" LenValue="10" />
  <xselement name="AckTransactionSetID" minOccurs="0" LenValue="3" />
  <xselement name="ApplicationAckCd" minOccurs="0" LenValue="2" />
  <xselement name="InsuredReportNum" minOccurs="0" LenValue="15" />
  <xselement name="ClaimAdminClaimNum" minOccurs="0" LenValue="25" />
  <xselement name="JuridictionClaimNum" minOccurs="0" LenValue="26" />
  <xselement name="PolicyNum" minOccurs="0" LenValue="25" />
  <xselement name="PolicyEffectiveDt" minOccurs="0" LenValue="8" />
  <xselement name="MaintenanceTypeCd" minOccurs="0" LenValue="2" />
  <xselement name="MaintenanceTypeCdDt" minOccurs="0" LenValue="8" />
  <xselement name="RequestCd" minOccurs="0" LenValue="10" />
  <xselement name="ReportText" minOccurs="0" LenValue="60" />
  <xselement name="NumOfErrors" minOccurs="0" LenValue="25" />
  <xselement name="MaintenanceTypeCorrectionCd" minOccurs="0" LenValue="2" />
  <xselement name="MaintenenceTypeCorrectionCdDt" minOccurs="0" LenValue="8" />
  <xselement name="JuridictionBrOfficeCd" minOccurs="0" LenValue="25" />
  <xselement name="JuridictionStateCd" minOccurs="0" LenValue="25" />
  <xselement name="ClaimAdminAltPostalCode" minOccurs="0" LenValue="25" />
</hello>

I want a select statement which produces

name

values as COLUMN1 and

LenValue

Values as COLUMN2
For Example:

TransactionSetID 3
SeqNum 9
...
...

Please advice.
Thanks,
Kumar

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-07-22 : 00:40:17

try this

DECLARE @XML XML
SELECT @XML = '<hello>
  <xselement name="TransactionSetID" minOccurs="0" LenValue="3" />
  <xselement name="SeqNum" minOccurs="0" LenValue="9" />
  <xselement name="ReportTypeCd" minOccurs="0" LenValue="25" />
  <xselement name="RecordTypeCd" minOccurs="0" LenValue="25" />
  <xselement name="WCTRPNum" minOccurs="0" LenValue="25" />
  <xselement name="WCISScreenName" minOccurs="0" LenValue="25" />
  <xselement name="DNName" minOccurs="0" LenValue="25" />
  <xselement name="ProcessDate" minOccurs="0" LenValue="8" />
  <xselement name="ProcessTime" minOccurs="0" LenValue="8" />
  <xselement name="InsurerFEIN" minOccurs="0" LenValue="10" />
  <xselement name="ClaimAdminPostalCode" minOccurs="0" LenValue="10" />
  <xselement name="ClaimAdminFEIN" minOccurs="0" LenValue="10" />
  <xselement name="AckTransactionSetID" minOccurs="0" LenValue="3" />
  <xselement name="ApplicationAckCd" minOccurs="0" LenValue="2" />
  <xselement name="InsuredReportNum" minOccurs="0" LenValue="15" />
  <xselement name="ClaimAdminClaimNum" minOccurs="0" LenValue="25" />
  <xselement name="JuridictionClaimNum" minOccurs="0" LenValue="26" />
  <xselement name="PolicyNum" minOccurs="0" LenValue="25" />
  <xselement name="PolicyEffectiveDt" minOccurs="0" LenValue="8" />
  <xselement name="MaintenanceTypeCd" minOccurs="0" LenValue="2" />
  <xselement name="MaintenanceTypeCdDt" minOccurs="0" LenValue="8" />
  <xselement name="RequestCd" minOccurs="0" LenValue="10" />
  <xselement name="ReportText" minOccurs="0" LenValue="60" />
  <xselement name="NumOfErrors" minOccurs="0" LenValue="25" />
  <xselement name="MaintenanceTypeCorrectionCd" minOccurs="0" LenValue="2" />
  <xselement name="MaintenenceTypeCorrectionCdDt" minOccurs="0" LenValue="8" />
  <xselement name="JuridictionBrOfficeCd" minOccurs="0" LenValue="25" />
  <xselement name="JuridictionStateCd" minOccurs="0" LenValue="25" />
  <xselement name="ClaimAdminAltPostalCode" minOccurs="0" LenValue="25" />
</hello>'

SELECT X.value('@name', 'VARCHAR(100)') AS 'name'
		, X.value('@minOccurs', 'VARCHAR(100)') AS 'minOccurs'
		, X.value('@LenValue', 'VARCHAR(100)') AS 'LenValue'
FROM @XML.nodes('/hello/xselement') e(X)

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources